How to Import Dividend Data into Google Sheets

Deepanshu Bhalla Add Comment

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.

  1. Historical Dividend Data
  2. Forward Annual Dividend Rate
  3. Forward Annual Dividend Yield
  4. 5 Year Average Dividend Yield
  5. Trailing Annual Dividend Rate
  6. Trailing Annual Dividend Yield
  7. Payout Ratio
  8. Ex-Dividend Date
Import Dividend Data into Google Sheets

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.
Historical Dividend Data in Google Sheets

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.

Key Dividend Statistics in Google Sheets
Google Apps Script

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];
}
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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.

Post Comment 0 Response to "How to Import Dividend Data into Google Sheets"
Next → ← Prev