Accessing dividend history data can be an incredibly powerful tool for investors looking to find undervalued stocks and ETFs. Knowing the dividend yield and past dividends, as well as the average dividends over a period of years, can provide important information to help you make the best investment decision. But how can you get this valuable dividend history data into your Excel or Google Sheets spreadsheet? Read on for our guide to unlocking the power of dividend history data.
What is dividend history data?
Dividend history data is a set of information about the dividend payments associated with a particular stock or ETF. This can include the amount paid out in dividends, when those payments were made, and other pertinent details such as the company's dividend payout ratio. Having this information at your fingertips can be invaluable for making informed decisions about which stocks and ETFs to invest in and sell.
Why is it important?
Having access to dividend history data can provide investors with an in-depth look at the company's performance over time. This includes being able to assess whether a stock or ETF is suitable for long-term investment or if it might be better suited as a short-term trade. This type of information can also help you spot potential trends in the company's performance, thereby giving you an edge over other investors.
Furthermore, dividend history data can provide you with insight into the company's dividend policy. This includes information on changes in dividends over time, as well as any special dividends that may have been paid out. Knowing this will give you a better understanding of the overall performance of the stock or ETF and help you decide whether it is a good investment for your portfolio.
Different forms of dividend history data
Several different metrics are very important when assessing dividend-paying stocks and ETFs. These include:
- Dividend yield: this is the ratio of the dividend divided by the share price and tells you how much money you are getting back for each share you own.
- Dividend growth: this measures how much the dividend has increased over time. It can tell you whether or not a company's dividends are growing or declining and will help you decide if it's worth investing in.
- Payout ratio: this is the ratio of dividends paid out versus what the company earns in profits. It tells you how much of its profits a company is paying out as dividends and can help you determine whether or not the company is over-distributing or under-distributing its cash flow.
- Average dividend: this is simply the average dividend that has been paid over a period of time. It can give you an idea of how much the company has been paying out in dividends each year and help you decide if it is a reliable source of dividend income.
- Dividend Coverage Ratio: Evaluate the company's ability to comfortably cover its dividend payments from its earnings, providing insight into the dividend's safety.
- Dividend History: Review the company's track record of consistent dividend payments and any increases, decreases, or suspension patterns.
- Earnings Per Share (EPS): Examine the company's profitability and its ability to generate sufficient earnings to support dividend payments.
- Cash Flow: Assess the company's cash flow generation to ensure that it has enough liquidity to sustain dividend payments.
- Debt-to-Equity Ratio: Evaluate the company's financial health by analyzing its level of debt in relation to its equity, as excessive debt could impact its ability to sustain dividends.
How can you access dividend history data in your spreadsheet?
There are many ways of accessing dividend data in your spreadsheet, such as a manual process or through an API. However, the easiest way is to use the Wisesheets Excel and Google Sheets add-in. Here are examples of how to get the most important dividend metrics on your spreadsheet through the add-in.
Real-time dividend yield
To get the real-time dividend yield of any stock and ETF across 50+global exchanges, all you need to do is enter the following formula on any cell of your spreadsheet:
=WISE("ticker", "dividend yield", "ttm").
For instance, to get the real-time dividend yield of Coca-Cola (KO), enter the following:
=WISE("KO", "dividend yield", "ttm")
As you can see, this will return the dividend yield for Coca-Cola (KO) in real time based on the latest dividend payments and stock price.
You can do the same for a list of stocks by simply setting up the same function as you can see on the image and dragging it down.
Allowing you to get the dividend yield of multiple stocks in an instant. At any moment, you can press the refresh live data button on the add-in to get the latest dividend yields for all your stocks and or ETFs.
Historical dividend yield
To get the historical dividend yield of any stock or ETF, all you need to do is enter the following formula on any cell of your spreadsheet:
=WISE("ticker", "dividend yield", year) or =WISE("ticker", "dividend yield", year, "quarter")
For instance, to get the historical dividend yield of Coca-Cola (KO) for the year 2021, enter the following:
=WISE("KO", "dividend yield", 2021)
This will return the dividend yield for Coca-Cola (KO) during the year 2021. You can also specify a specific quarter in order to get more granular data. For example, to get the dividend yield in the first quarter of 2021, enter the following:
=WISE("KO", "dividend yield", 2021, "Q1")
This will provide you with the dividend yield based on the dividend payments made and the closing stock price when the financial statements were published.
Historical dividend data
Getting historical dividend payments of an ETF or stock is super easy. All you have to do is enter the following formula on any cell of your spreadsheet:
=WISEPRICE("ticker", "dividend")
For instance, to get the historical dividend payments of Coca-Cola (KO), enter the following:
=WISEPRICE("KO", "dividend")
This will return all the historical dividends paid out by Coca-Cola (KO), including the ex-date, dividend payment, and declaration date. This data can be used to calculate other metrics like dividend growth, average dividend, or payout ratio. As well as any visualizations to make sense of the data.
Dividend payout ratio
To get the dividend payout ratio of any stock and ETF, enter the following formula on any cell of your spreadsheet:
=WISE("ticker", "payout ratio", "ttm"), =WISE("ticker", "payout ratio", year) and =WISE("ticker", "payout ratio", year, "quarter")
For instance, to get the dividend payout ratio of Coca-Cola (KO), enter:
=WISE("KO", "payout ratio", "ttm")
This will return the current ttm dividend payout ratio for Coca-Cola (KO). You can also specify a specific year or quarter to get the past dividend payout ratio. For example, to get the dividend payout ratio for CocaCola (KO) during Q1 2021, enter:
=WISE("KO", "payout ratio", 2021, "Q1")
This will provide you with the dividend payout ratio based on the dividend payments and reported earnings at the time.
Earnings per share
To get the earnings per share (EPS) of any stock, enter the following formula on any cell of your spreadsheet:
=WISE("ticker", "eps", "ttm"), =WISE("ticker", "eps", year) and =WISE("ticker", "eps", year, "quarter").
For instance, to get the EPS of Coca-Cola (KO) for 2021, enter:
=WISE("KO", "eps", 2021)
This will return the reported EPS for Coca-Cola (KO) during the year 2021. You can also specify a specific quarter in order to get more accurate data. For example, to get the EPS in the first quarter of 2021, enter the following:
=WISE("KO", "eps", 2021, "Q1")
This will provide you with the reported EPS for CocaCola (KO) during Q1 2021. This data can be used alongside dividend payments and stock prices to calculate the historical dividend yield of the stock.
Strategies for Utilizing Dividend History Data in Your Investment Decisions
The best way to use this data to inform your investment decisions is by creating a dividend portfolio. This means looking for companies that have historically consistent dividends and strong earnings in order to ensure a steady income stream. Additionally, you can analyze the dividend history data of any company or ETF in order to identify patterns of increases, decreases, suspensions, etc., which will help you decide whether or not to invest in a particular company. Finally, it is essential to look at a company's debt in relation to its equity, as excessive debt could impact its ability to sustain dividends.
By using the Wisesheets Excel and Google Sheet add-in, you will have instant access to dividend history data such as dividend yield, payout ratio, EPS, and dividend payments for any stock or ETF across fifty global exchanges. This data can be used to inform your investment decisions and help you identify dividend-paying stocks that offer consistent payouts and steady returns. With this knowledge, you can build a portfolio of dividend-paying stocks that will generate consistent income over time.
Hopefully, this guide has been helpful, and you have learned how to access dividend history data for any stock or ETF in your Excel or Google Sheets.
To your investment success!
