In this post, we will discuss how to pull stock dividend information into Google Sheets for free.
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) {
outputData.getRange(“A2:H” + outputLastRow).clearContent();
}
// Loop over multiple symbols
for (var i = 8; i 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];
}
Read MoreListenData
