Monitoring a company’s dividend payments is crucial for income investors seeking to maximize their returns. While tracking dividends across multiple holdings can be time-consuming, the Google Sheets Dividend Tracker, powered by dynamic EODHD APIs, simplifies the process.
In this guide, we’ll walk through how to build a Google Sheets Dividend Tracker Template that pulls key data from EODHD’s APIs, allowing you to track dividends for any public company. EODHD provides simple JSON APIs covering dividend payments, yields, ex-dates, and more for 150,000+ tickers across 70+ worldwide exchanges.
You can also check our template libraries with step-by-step guides for Excel and Google Sheets spreadsheets. Here you’ll find more information on our Ready-to-Go solutions.
Check our Financial Analysis Google Sheets Template to follow the article:
Quick jump:
Sign up for EODHD and get your API key
Register for a free EODHD account at https://eodhd.com/register to receive your API key. The free plan allows 20 API requests/day and 1 year of EOD historical data. Paid plans starting at $19.99/month enable more API calls and access to 30+ years of data.
To follow the article and test the basic functionality of our APIs you can use our “demo” key. The key gives you access to the Fundamentals and Historical data for AAPL, TSLA, MSFT, and MCD tickers.
Pull in data from EODHD to Google Sheets
The main tool to retrieve data into the Google Sheets Dividend Tracker Template cells from an API is the “=IMPORTDATA()” Google Sheets formula. By using the formula you can get access to all our Fundamental And Historical APIs. Thus, It is important to check the official Google Sheets Documentation.
Use the Historical Splits and Dividends API to retrieve the dividends values over a certain period of time for your chosen stock:
- “https://eodhd.com/api/div/”&B7&”?from=”&TEXT(D7,”YYYY-MM-DD”)&”&to=”&IF(E7=0, TEXT(TODAY(),”YYYY-MM-DD”),TEXT(E7,”YYYY-MM-DD”))&”&api_token=”&$B$3: This part constructs the URL for the API request. It concatenates various parameters:
- B7 contains the ticker symbol.
- D7 contains the start date of the data range for dividends.
- E7 contains the end date of the data range for dividends. If E7 is zero (or blank), it uses today’s date.
- $B$3 contains an API key. To get full access please subscribe to one of EODHD’s monthly plans.
- IMPORTDATA: This function retrieves data from the specified URL.
The URL constructed by the formula requests dividend data for a specific ticker symbol (B7) within a specified date range (D7 to E7, or from D7 to today’s date if E7 is zero) using an API token ($B$3).
Add Ratios to the Dividend Tracker
To calculate ratios for a specific period we need to pull data from the Historical Dividend and the End-of-Day historical API. The Google Sheets cell formula for the Combined Dividend Payouts is the following:
=SUM(TRANSPOSE((INDEX(IMPORTDATA("https://eodhd.com/api/div/"&B7&"?from="&TEXT(D7,"YYYY-MM-DD")&"&to="&IF(E7=0, TEXT(TODAY(),"YYYY-MM-DD"),TEXT(E7,"YYYY-MM-DD"))&"&api_token="&$B$3),,2))))*C7
For the Combined Dividend Yield:
=(F7/C7)/INDEX(IMPORTDATA("https://eodhd.com/api/eod/"&B7&"?from="&TEXT(D7,"YYYY-MM-DD")&"&to="&TEXT(D7+10,"YYYY-MM-DD")&"&api_token="&$B$3&"&filter=close&fmt=csv"),2,)
We need to use Fundamentals API for current ratios which will show precalculated ratios pulled straight from the EODHD platform. The cell formula for the Current Dividend/Share:
=IMPORTDATA("https://eodhd.com/api/fundamentals/"&B7&"?api_token="&$B$3&"&filter=Highlights::DividendYield")
Current Dividend Yield:
=IMPORTDATA("https://eodhd.com/api/fundamentals/"&B7&"?api_token="&$B$3&"&filter=Highlights::DividendShare")
Add Sparklines and Charts
Sparklines could be used to demonstrate performance over time without uploading the preformatted data from the APIs into cells. Sparkline Cell formula for combined Yield:
=SPARKLINE((INDEX(IMPORTDATA("https://eodhd.com/api/eod/"&B7&"?from="&TEXT(D7,"YYYY-MM-DD")&"&to="&IF(E7=0, TEXT(TODAY(),"YYYY-MM-DD"),TEXT(E7,"YYYY-MM-DD"))&"&api_token="&$B$3),,6)))
Select the range and insert the chart to visualize historical performance or to compare the performance of the holdings against each other. It is better to locate historical data for a specific ticker on a newly created separate bookmark to have a clean dividend portfolio dashboard.
Conclusion
With this template powered by EODHD’s APIs, you can quickly access and analyze the financials for any stock to uncover crucial insights. The EODHD platform offers:
- Worldwide coverage: 150,000+ stocks, 20,000+ ETFs, 1000+ indices across 70+ exchanges
- Deep history: 30+ years of fundamentals and EOD pricing data
- Google Sheets-friendly: Simple APIs that work seamlessly with the IMPORTDATA() cell formula
- Low cost: Plans from $19.99/month
EODHD’s financial data APIs combined with Google Sheets’ analysis capabilities provide a powerful yet accessible toolset for in-depth financial statement analysis. This templated approach streamlines the data gathering to let you focus on uncovering actionable insights to drive better investment decisions.
Feel free to contact our support team at support@eodhistoricaldata.com for any questions or current discount offers. We’re happy to assist you through the process of leveraging EODHD’s data to elevate your investment workflow.
