In this post, we will discuss how to pull stock dividend information into Google Sheets for free.
In Google Sheets, we can use the GOOGLEFINANCE function to get stock prices but for detailed dividend information (dividend yield or history), we will need to use external data sources like Yahoo Finance. We can use Google Apps Script to automate pulling data from Yahoo Finance and filling it into Google Sheets.
How to Use the File : First make a copy by selecting File > Make a copy. While running the script, Google will ask you to authorize Appscript once.
We will import the following data from YahooFinance into Google Sheets.
- Historical Dividend Data
- Forward Annual Dividend Rate
- Forward Annual Dividend Yield
- 5 Year Average Dividend Yield
- Trailing Annual Dividend Rate
- Trailing Annual Dividend Yield
- Payout Ratio
- Ex-Dividend Date
Historical Dividend Data
It includes information about dividends that a company has paid out to its shareholders between two specific period of time. The end date argument is not included in the range.
Once you click on the Get Historical Data button, dividend data will be fetched from yahoo finance and will appear in the "HistoricalData" sheet tab.
This google sheet tool gets dividend information for hundreds of thousands of stocks from 48 countries. Make sure to use the correct ticker format from Yahoo Finance. For US stocks, just enter the ticker symbols but for stocks outside the US, you will need to add a suffix as shown in the table below.
Example : The ticker for LVMH is MC. So we need to input MC.PA as 'PA' refers to the exchange in france.
| Market | Exchange | Suffix |
|---|---|---|
| Argentina | Buenos Aires Stock Exchange (BYMA) | .BA |
| Austria | Vienna Stock Exchange | .VI |
| Australia | Australian Stock Exchange (ASX) | .AX |
| Belgium | Euronext Brussels | .BR |
| Brazil | Sao Paolo Stock Exchange (BOVESPA) | .SA |
| Canada | Canadian Securities Exchange | .CN |
| Canada | NEO Exchange | .NE |
| Canada | Toronto Stock Exchange (TSX) | .TO |
| Canada | TSX Venture Exchange (TSXV) | .V |
| Chile | Santiago Stock Exchange | .SN |
| China | Shanghai Stock Exchange | .SS |
| China | Shenzhen Stock Exchange | .SZ |
| Czech Republic | Prague Stock Exchange Index | .PR |
| Denmark | Nasdaq OMX Copenhagen | .CO |
| Egypt | Egyptian Exchange Index (EGID) | .CA |
| Estonia | Nasdaq OMX Tallinn | .TL |
| Europe | Euronext | .NX |
| Finland | Nasdaq OMX Helsinki | .HE |
| France | Euronext Paris | .PA |
| Germany | Berlin Stock Exchange | .BE |
| Germany | Bremen Stock Exchange | .BM |
| Germany | Dusseldorf Stock Exchange | .DU |
| Germany | Frankfurt Stock Exchange | .F |
| Germany | Hamburg Stock Exchange | .HM |
| Germany | Hanover Stock Exchange | .HA |
| Germany | Munich Stock Exchange | .MU |
| Germany | Stuttgart Stock Exchange | .SG |
| Germany | Deutsche Boerse XETRA | .DE |
| Greece | Athens Stock Exchange (ATHEX) | .AT |
| Hong Kong | Hong Kong Stock Exchange (HKEX)*** | .HK |
| Hungary | Budapest Stock Exchange | .BD |
| Iceland | Nasdaq OMX Iceland | .IC |
| India | Bombay Stock Exchange | .BO |
| India | National Stock Exchange of India | .NS |
| Indonesia | Indonesia Stock Exchange (IDX) | .JK |
| Ireland | Euronext Dublin | .IR |
| Israel | Tel Aviv Stock Exchange | .TA |
| Italy | EuroTLX | .TI |
| Italy | Italian Stock Exchange | .MI |
| Japan | Tokyo Stock Exchange | .T |
| Latvia | Nasdaq OMX Riga | .RG |
| Lithuania | Nasdaq OMX Vilnius | .VS |
| Malaysia | Malaysian Stock Exchange | .KL |
| Mexico | Mexico Stock Exchange (BMV) | .MX |
| Netherlands | Euronext Amsterdam | .AS |
| New Zealand | New Zealand Stock Exchange (NZX) | .NZ |
| Norway | Oslo Stock Exchange | .OL |
| Portugal | Euronext Lisbon | .LS |
| Qatar | Qatar Stock Exchange | .QA |
| Russia | Moscow Exchange (MOEX) | .ME |
| Singapore | Singapore Stock Exchange (SGX) | .SI |
| South Africa | Johannesburg Stock Exchange | .JO |
| South Korea | Korea Stock Exchange | .KS |
| South Korea | KOSDAQ | .KQ |
| Spain | Madrid SE C.A.T.S. | .MC |
| Saudi Arabia | Saudi Stock Exchange (Tadawul) | .SAU |
| Sweden | Nasdaq OMX Stockholm | .ST |
| Switzerland | Swiss Exchange (SIX) | .SW |
| Taiwan | Taiwan OTC Exchange | .TWO |
| Taiwan | Taiwan Stock Exchange (TWSE) | .TW |
| Thailand | Stock Exchange of Thailand (SET) | .BK |
| Turkey | Borsa İstanbul | .IS |
| United Kingdom | London Stock Exchange | .L |
| Venezuela | Caracas Stock Exchange | .CR |
Note : The macro returns NA if no data is found on Yahoo Finance.
Key Dividend Statistics
Key statistics related to dividends are downloaded from Yahoo Finance when you click on the "Get Key Dividend Metrics" button in Google Sheets.
The following Google Apps Script is used to import historical dividend data from Yahoo Finance into Google Sheets.
function getData() {
var inputControls = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inputs");
var outputData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HistoricalData");
var lastRow = inputControls.getLastRow();
// Arguments
var startDate = Math.floor((inputControls.getRange("B4").getValue() - new Date('January 1, 1970')) / 1000);
var endDate = Math.floor((inputControls.getRange("B5").getValue() - new Date('January 1, 1970')) / 1000);
var dateDifference = inputControls.getRange("B5").getValue() - inputControls.getRange("B4").getValue();
if (inputControls.getRange("B5").getValue() > new Date()) {
var result = Browser.msgBox("EndDate seems greater than today's date. Okay to you?", Browser.Buttons.YES_NO);
if (result == 'no') return;
}
if (dateDifference < 1) {
Browser.msgBox("Date difference must be at least one. Since EndDate is not inclusive of the date, you can have one day difference between start and end Date to fetch latest price");
return;
}
var outputLastRow = outputData.getLastRow();
if (outputLastRow > 1) {
outputData.getRange("A2:H" + outputLastRow).clearContent();
}
// Loop over multiple symbols
for (var i = 8; i <= lastRow; i++) {
var symbol = inputControls.getRange("A" + i).getValue();
var offsetCounter = 1;
extractData(symbol, startDate, endDate, offsetCounter, outputData);
}
var rng = outputData.getRange("A:D");
rng.removeDuplicates([1, 3, 4]);
// Select the outputData sheet after the script finishes
SpreadsheetApp.setActiveSheet(outputData);
}
function extractData(symbols, startDate, endDate, offsetCounter, outputData) {
var tickerURL = "https://query1.finance.yahoo.com/v8/finance/chart/" + symbols +
"?events=capitalGain%7Cdiv%7Csplit&formatted=true&includeAdjustedClose=true&interval=1d&period1=" + startDate +
"&period2=" + endDate;
var response = UrlFetchApp.fetch(tickerURL);
var data = JSON.parse(response.getContentText());
var combinedData = extractDividends(data, symbols);
if (!combinedData || typeof combinedData === 'string') {
outputData.getRange(outputData.getLastRow() + offsetCounter, 1, 1, 4).setValues([["NA", "NA", "NA", symbols]]);
} else {
outputData.getRange(outputData.getLastRow() + offsetCounter, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
}
}
function extractDividends(data, symbol) {
if (!data.chart || !data.chart.result || data.chart.result.length === 0) {
return "No dividends found.";
}
var result = [];
var dividends = data.chart.result[0].events && data.chart.result[0].events.dividends; // Check if events exists
if (!dividends) {
return "No dividends found."; // Return if dividends is undefined
}
var exchangeTimezoneName = data.chart.result[0].meta.exchangeTimezoneName;
var currency = data.chart.result[0].meta.currency;
for (var date in dividends) {
if (dividends.hasOwnProperty(date)) {
var dividend = dividends[date];
result.push([
formatDate(new Date(date * 1000), exchangeTimezoneName), // Format date
dividend.amount,
currency,
symbol
]);
}
}
return result.length > 0 ? result : "No dividends found."; // Return result or a message
}
function formatDate(date, timezone) {
// Convert date to local timezone if necessary
return date.toISOString().split('T')[0];
}
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Share Share Tweet