Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Get Dividend Data in Google Sheets

Cody Schneider••7 min read

Tracking stock dividends directly in Google Sheets is a great way to monitor your portfolio's income. While Google's built-in functions can get you part of the way, pulling specific historical dividend amounts and dates requires a few clever tricks. This tutorial will walk you through several methods, from simple built-in formulas to more advanced web scraping, to build a powerful and custom dividend tracker.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Using the GOOGLEFINANCE Function for Basic Dividend Info

The most direct way to get financial data into Google Sheets is with the native GOOGLEFINANCE function. It can instantly fetch current and historical market information, including a few key dividend-related metrics.

What You Can Get (and What You Can't)

While powerful, it's important to understand that GOOGLEFINANCE cannot directly retrieve a historical list of dividend payment dates and amounts. This is a common point of frustration for users, but don’t worry, we'll cover workarounds later.

Here are the key dividend-related data points you can get with this function:

  • Dividend Yield (yieldpct): The annual dividend per share as a percentage of the stock's current price.
  • Earnings Per Share (eps): The portion of a company's profit allocated to each outstanding share of common stock.
  • Price-to-Earnings Ratio (pe): The company's share price relative to its earnings per share.

How to Use It: Step-by-Step

Let's grab the current dividend yield for Apple Inc. (AAPL). The structure of the formula is =GOOGLEFINANCE("Ticker", "Attribute").

  1. Click on an empty cell in your Google Sheet.
  2. Type the following formula. You need to include the exchange symbol (like NASDAQ) for the best results.
  3. Press Enter. Google Sheets will automatically display the current dividend yield of Apple stock in that cell.

You can use this same format for other tickers and attributes. For example, to get the P/E ratio for Johnson & Johnson (JNJ):

=GOOGLEFINANCE("NYSE:JNJ", "pe")

While useful for getting a snapshot, the GOOGLEFINANCE function alone isn't enough to build a detailed history of dividend payments for your analysis. For that, we need to pull data from external websites.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Scraping Historical Dividends with IMPORT Functions

To get historical dividend dates and amounts, you can use Google Sheets' powerful import functions: IMPORTHTML and IMPORTXML. These formulas let you pull structured data directly from financial websites into your spreadsheet, and they are perfect for this task.

Method 1: Using IMPORTHTML

The IMPORTHTML function is designed to import data from a table or a list within an HTML page. This is often the easiest web scraping method because you only need to identify the correct table number on the page.

For our example, we will pull dividend history from MarketWatch.

Step 1: Find Your Data Source URL

First, find the dividend history page for the stock you want to track. The MarketWatch URL for a stock's dividend history typically follows this format: https://www.marketwatch.com/investing/stock/TICKER/dividendhistory.

For Apple (AAPL), the URL would be: https://www.marketwatch.com/investing/stock/aapl/dividendhistory.

Step 2: Construct the Formula

The formula's structure is =IMPORTHTML("URL", "query_type", "index").

  • URL: The webpage address (the one from Step 1).
  • Query Type: Either "table" or "list". We want to scrape a table.
  • Index: The number of the table on the page you want to pull, starting from 1. Most financial pages only have one main data table, so this is usually 1.

Let's put it together. In an empty cell, enter this formula:

=IMPORTHTML("https://www.marketwatch.com/investing/stock/aapl/dividendhistory", "table", 1)

After pressing Enter, Google Sheets will populate the cells with the full dividend history table from the MarketWatch page, including Ex-Dividend Date, Pay Date, Amount, and Declaration Date. You now have the historical data that GOOGLEFINANCE couldn't provide!

Note: Web scraping can sometimes break if the source website changes its page structure. If the formula ever stops working, revisit the page to see if the table index number has changed.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Method 2: Using IMPORTXML

The IMPORTXML function is more precise but requires a little more technical know-how. Instead of pulling an entire table, it lets you target very specific data using something called an "XPath query." Think of XPath as a specific address for an element on a webpage.

This method is useful if a website's data isn't in a clean table format or if you want to pull isolated data points.

Step 1: Find the XPath Query

  1. Navigate to the dividend history page, like the MarketWatch URL for Apple.
  2. Right-click on the specific data point you want to grab (e.g., the first dividend amount in the table).
  3. Select Inspect from the dropdown menu. This will open your browser's developer tools.
  4. The HTML code for the element you clicked will be highlighted. Right-click this highlighted line of code.
  5. Go to Copy > Copy XPath. This copies the unique path to that element to your clipboard.

For the table data on MarketWatch, a working XPath might look like this: //td[contains(@class,'table__cell')]. This grabs all table cell elements (<td>) that have a certain class name.

Step 2: Build the Formula

The formula structure is =IMPORTXML("URL", "xpath_query"). Using the MarketWatch URL and the XPath, it looks like this:

=IMPORTXML("https://www.marketwatch.com/investing/stock/aapl/dividendhistory", "//td[contains(@class,'table__cell')]")

This command pulls all matching data points into a single column. It's less formatted than IMPORTHTML but gives you surgical precision. You can also use functions like WRAPROWS to reformat the single column output into a proper table.

Creating a Dynamic Dividend Tracker Template

Now let's apply these formulas to build a simple but effective dividend tracker.

  1. Set Up Your Headers: In the first row of a new sheet, create columns like: Ticker, Company Name, Shares Owned, Current Price, Last Dividend, Annual Income, Dividend Yield.
  2. Enter Tickers and Shares:
  3. Fetch Dynamic Data with Formulas:

You can now drag these formulas down the rows for each ticker in your portfolio, and your tracker will automatically fetch and calculate the dividend data for each holding.

For Advanced Users: Google Apps Script

If you find that IMPORT functions are unreliable or you want more control, you can use Google Apps Script. This involves writing a short bit of JavaScript code to create your own custom functions.

The main advantages of Apps Script are:

  • It can parse sites that rely heavily on JavaScript, which often breaks standard IMPORT formulas.
  • You can interact with official financial data APIs for more reliable data fetching.
  • Your code is less likely to break from minor website layout changes.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

How to Create a Simple Custom Function

  1. From your Google Sheet, go to Extensions > Apps Script.
  2. This opens a new script project. Delete any placeholder code and paste in the following function. This example script fetches the HTML from MarketWatch and uses a simple text search (RegEx) to find the first dividend amount it sees.
function GETLATESTDIVIDEND(ticker) {
  if (!ticker) {
    return "Ticker is required.",
  }
  
  var url = 'https://www.marketwatch.com/investing/stock/' + ticker + '/dividendhistory',
  
  try {
    var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true}),
    var content = response.getContentText(),
    
    // Look for a div containing "key-data", then find the dividend amount inside.
    // This looks for a string like: <td class="table__cell align--right">$0.25</td>		
    var regex = /<td class="table__cell[^"]*">\s*\$([\d\.]*)\s*<\/td>/,
    var match = content.match(regex),
    
    if (match && match[1]) {
      return parseFloat(match[1]),
    } else {
      return "Amount not found.",
    }
  } catch(e) {
    return "Error fetching data.",
  }
}
  1. Click the Save icon (it looks like a floppy disk).
  2. Return to your spreadsheet. You can now use this as a custom function! In any cell, type: =GETLATESTDIVIDEND("AAPL"). It will run your script and return the latest dividend amount.

While more involved, Apps Script unlocks a new level of customization and reliability for automating your financial data tracking.

Final Thoughts

Tracking your stock dividend data in Google Sheets can range from using simple, powerful functions like GOOGLEFINANCE to building sophisticated web scrapers with IMPORTHTML or custom Apps Scripts. By combining these methods, you can create a dynamic and automated dashboard to monitor one of the most important aspects of your investment portfolio.

All of these methods require some setup and ongoing tweaking as data sources change. We built Graphed to remove this kind of manual effort. Instead of writing formulas or code, you can connect your data sources (even a live Google Sheet) and use natural language to create dashboards and reports. You simply ask questions like, "Show me my top 5 stocks by dividend yield," and Graphed builds the charts for you, so you can spend less time wrangling data and more time finding insights.

Related Articles