The stock market is off to a rough start to 2022, with the S&P 500 falling more than 5% in just one month. Using a spreadsheet, we can analyze historical trends and patterns to identify what normally happens after such bad starts. Below, I’ll use data from Google Sheets to pull in historical values and analyze how the index has performed afterward and whether this year is doomed to be a bad year, or if a recovery is likely and if now is a good time to invest in stocks.
Start with downloading the historical data
The first step is to get the S&P 500’s historical values in Google Sheets. This can be done using the GOOGLEFINANCE function. Using the .INX symbol, I can calculate the S&P 500 values going back to the 70s. Here’s a matrix showing the returns over the past 50 years, after applying some conditional formatting to the values:
Filtering the data
To zero on in just the largest January declines, I can use the Filter by condition option to specify January values where the percent change is less than negative 5%:
That leaves me with the years when the S&P 500 dropped by 5% or more in the first month:
Now that I have a list of the years I’m looking to analyze, I can start creating some charts.
Using charts to summarize the performances
The first visual I’m going to create will look at how the index has performed after January, after those bad starts. To do that, I need to take the year-end values and divide them by the values at the end of January. This tells me how much the index rose or declined in the remaining months. And when grouping those variances, this is what the data shows:
Of the 7 previous times when the S&P 500 dropped 5% in January, 3 times it would continue to drop in the following months and finish even lower. Only two times would the index rise by more than 10%. I can also average the results, comparing the down years versus the overall average:
This tells me that in a year where the S&P 500 typically tanks in the first month, the overall returns from the index are likely to be negative. However, to add a bit more context to this, I’ll look at the individual returns by year and compare them against the 50-year average, which is summarized in this table:
By keeping the average column constant, it creates a straight line for the chart and makes it easy to visualize the individual years’ returns and how they compare against it:
A few of the things that stand out from the data is that in three of the years (2000, 2008, 2009), the markets were either in the midst of a significant crash or recovering from it. It helps put into context some of these returns, suggesting that the other years might indicate more typical returns in a non-crash year. And if that’s the case, investors may expect fairly modest returns this year, possibly negative ones overall. Although it isn’t a large data set, it certainly suggests that the stock market may be facing a down year in 2022.
If you liked this post on the S&P 500’s Historical Returns, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Have you ever wondered how a stock has typically performed month over month? Using a spreadsheet, you can calculate monthly returns and identify patterns of which months are traditionally strong for a stock, and which ones aren’t. In this example, I’m going to use Google Sheets to pull in stock prices and calculate historical stock returns by month.
Start with pulling in historical stock prices
To get started, I’ll need to extract a stock’s price history. This can be done using Google Sheets’ GOOGLEFINANCE function. The key is in setting a start date that goes far enough back to ensure you get enough historical data to use in your calculations. A good function to use within that is the TODAY() function which ensures you will always be counting backward from today’s date and don’t need to hardcode a date. If I want to go back to 2008, for example, I can set my formula to deduct about 5,000 days.
To pull Amazon’s stock price going back that far, this is what my formula would look like in Google Sheets:
The one problem here is that the date values contain the time, 16:00:00, which represents the 4 pm closing time of the stock market. I only want the actual date since I’m going to be doing a lookup and don’t want to include time. To extract just the date, I can use the DATE() function and use the YEAR(), MONTH() and DAY() functions to refer back to the values in column A. For example:
=DATE(YEAR(A2),MONTH(A2),DAY(A2))
The above formula would give me the date in column A without the time. I’ll add an IF statement at the start so that in case the value in column A is blank, my formula simply won’t compute anything:
=if(A2="","",DATE(YEAR(A2),MONTH(A2),DAY(A2)))
Now I have a table that has just date values without any time:
Creating a date matrix
Next, what I’m going to do is create a matrix that has years going vertically and months going across:
I’m going to fill in these values with the stock’s returns in each of those months. The key to making this work is by using the DATEVALUE() function which allows me to enter a date. For example, if I entered the following formula:
=DATEVALUE("Jan 1, 2022")
It would result in the following output:
1/1/2022
In the first cell of my matrix, for the JAN 2021, I’ll combine the month abbreviation (JAN) with the year (2021) and the first day (1). Here’s how that would work if the month name is in cell F1 and the year is in E2:
=DATEVALUE(F$1&" 1, "&$E2)
However, let’s assume I don’t want to pull the first day of the month and instead want to pull the ending month’s value. For that, I can use the EOMONTH() function. And then I would enclose the current formula within that:
=EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0)
The 0 value at the end indicates how many months I want to jump by. And since I just want the end of the current month, I don’t need to jump by any months, which is why I set it to 0. At this point, I have a date, and now I can use this inside of a MATCH() function to find the row that matches this date. Assuming the date values in are column C, here is the formula:
Now the formula will pull in the price for a given month. But if I want the month-over-month return, I need to take the month-end price and divide it by the previous month’s ending value. To get the previous month’s price, I use the same formula except instead of a 0, I’ll enter -1 for the number of months:
I’ll combine the two formulas now, taking the current month-end price and dividing it by the previous month’s value and also deduct -1 at the end to adjust for it being a percent-change calculation:
Now, copying this formula across the entire matrix, I can see the stock’s historical returns by month. I’ve added some conditional formatting to contrast the good months from the bad ones:
Besides relying on colors, I can also add a win rate % where I can count the times where the return was more than 0% (i.e. a ‘win’) and divide this by the total number of values. In column F, for January, the formula looks like this:
=COUNTIF(F2:F12,">0")/COUNTA(F2:F12)
I’ll also average the returns so that it’s easier to see the best and worst-performing months:
Judging from this, April looks to be the best time to own Amazon’s stock. It normally returns a positive return and its average over the past 11 years has been a gain of just under 8.5%. To re-create this analysis for other stocks, simply change the ticker symbol.
If you liked this post on How to Calculate Historical Stock Returns by Month, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
The MACD line and chart is a popular tool for technical analysts who buy and sell stocks. And in this post, I’ll show you how you can create it from start to finish. In my example, I’ve downloaded Apple’s stock price history for the past year from Yahoo Finance, and I’ll use that to calculate its MACD line. Here’s a sample of what I’m starting with:
Calculating the exponential moving averages
To calculate the MACD line, I’ll need to create multiple exponential moving averages (EMAs). One for 9 days, 12 days, and for 26 days. The logic will be the same so I can start with creating a formula for the 9-day EMA and then apply that to the others.
I’m going to create a couple of variables. The first being the n for the number of days. And the second one is for the weighting that you’ll apply to more recent values, and thus, turning it from a simple moving average into an exponential one. The weighting is calculated as follows:
=2/(1+n)
I’ll start my formulas to calculate the 9-day EMA by first checking to see if I have at least 10 data points. If I don’t, then I’m only calculating a simple moving average. Here’s how the start of that formula looks, assuming my closing stock prices start from cell B5 and my variable n is in cell C1:
IF(COUNTA($B$5:$B5)<=C$1,AVERAGE($B$5:$B5)
A key part of the formula is freezing cells properly. Cell $B$5 won’t move, but $B5 will as I drag it down. And this allows me to calculate the cumulative number of data points, and the corresponding average. The next part of the formula is what happens if I have more than nine data points. In that case, I will take the weighting (this is cell C2) on my sheet, and multiply that by the difference between the most recent stock price and the previous EMA. This will then get added to the previous day’s EMA:
C$2*($B5-$C4)+$C4
Column C is the one that contains the EMAs. My complete formula is as follows:
I can now copy this logic across multiple columns to calculate the 12 and 26 day EMAs as well:
Now, I’ll set up the calculations for the final three columns:
MACD: This involves taking the 12-day EMA and subtracting the 26-day EMA from that.
Signal Line: This is a 9-day EMA of the MACD line.
Histogram: This is calculated as the difference between the MACD line and the Signal line.
With all those columns set up, here is my completed table:
Creating the charts
With all the columns set up, the next part is to put the key data into a chart to illustrate the MACD line, Signal line, and Histogram. To make the chart look like a typical MACD chart, I’ll need to set the MACD line and Signal line to be line charts, and for the Histogram to be a column chart.
Initially, when the chart is created, there’s too much data in there since the data set is bigger than it needs to be:
To fix this, I right-click on the chart and click Select Data. Then, I remove all the series except for the last three: MACD line, Signal line, and Histogram. My updated chart looks as follows:
There are still a few more changes that I am going to make here. The first is to fix the axis, as there are gaps between the column charts. That’s because Excel is recognizing the axis as a date axis. And while that’s correct, that means there will be gaps since stocks don’t trade every day of the week, and thus, those gaps, are weekends. To fix this, right-click on the axis and click Format Axis. And then, change the Axis Type so that it is a Text axis:
And then, under the Labels section, I set the position so that it is Low and at the bottom of the chart. My updated chart looks a bit better:
The last change you may want to consider is adjusting the column chart gap, to shrink it so the chart looks more like a histogram. If you right-click on them and click Format Data Series, there’s an option to change the Gap Width. I find that setting this to 50% normally results in a good gap size::
And now we’ve got a chart that resembles what you might find on major finance sites when looking at MACD. If you want to follow along with the sheet that I’ve created, you can download my MACD chart template here.
If you liked this post on How to Create a MACD Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Correlations can be helpful in determining if there is a pattern or relationship between two sets of data. It can be useful when looking at stocks as those that are highly correlated may move together in the same direction (note: this doesn’t mean their returns will be the same). And if you want to diversify, that’s not what you’ll want to accomplish. Instead, negatively correlated investments or ones that aren’t correlated at all may be more preferable in that situation. Below, I’ll show you how you can easily calculate correlations between multiple stocks from data that you can download from a source like Yahoo Finance.
Downloading the data
The first thing that’s needed when running correlations is to download at least two sets of data. In this example, I’ll download data for five stocks: Pfizer, Procter & Gamble, Tesla, Exxon Mobil, and Alphabet. None of those stocks is terribly similar to one another so there should be some decent diversification there.
Below, I’ve downloaded all the closing prices from Yahoo Finance for all of 2021. Here’s what that data looks like:
The key is you want to make sure that the data is the same; you don’t want to have one stock showing a price at a different date than the other. They all need the same baseline. And that’s what the date field serves to do here. It itself isn’t necessary for the correlation calculation, but it’s just there to ensure that when you’re downloading and matching up data, everything lines up correctly to the right date.
The CORREL() funciton is useful for doing just a quick correlation calculation
Using Excel’s CORREL function, you can quickly calculate the correlation between two stocks. Pfizer is in column B and Procter & Gamble is in column C. If I wanted to quickly calculate their price correlation, my formula would be as follows:
=CORREL(B:B,C:C)
It doesn’t matter which order the data is in but there are only two ranges that are used as arguments in this function. This formula tells me there is a 94% correlation between these two stocks, at least, over the past year. That’s incredibly high and it could be because these are two fairly safe, value-oriented investments. Now, I could repeat this process for the other stocks here but there’s a quicker way to do that.
Using the Data Analysis option
Excel has some built-in Add-ins that you can enable that can quickly do tasks like this for you. You can access the Excel Add-ins from the Developer tab or by going through File->Options->Add-ins->Excel Add-ins. Either approach will get you to the same place. And once you’re there, you just need to check off the option for the Analysis Toolpak:
Once enabled, you will see the Data Analysis option on the Data tab. Clicking on that will give you many different options, including to do a Correlation:
On the next screen, I’ll have the option to select an Input Range. Here, I can select more than just two columns:
After clicking OK, Excel generates the correlation matrix for me in a new tab, saving me the time of doing all the calculations myself:
The lowest correlation noted here is between Tesla and Exxon Mobil, while the highest is between Pfizer and Procter & Gamble.
If you liked this post on How to Calculate Correlations Between Stocks, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Billionaire investor Warren Buffett is the CEO of Berkshire Hathaway. And what trades his company is making is always big news among his followers and fans. In this post, I’ll show you how you can use Excel, and specifically Power Query, to determine what the company’s holdings are as of their most recent quarter, and how much they’ve changed over time.
Start with getting the most recent 13f report
Berkshire Hathaway reports its holdings every quarter and you can find those filings on the SEC website. This link filters out the 13f filings for you already. I’m going to click on the Documents button of the most recent report:
On the next page, the key link to use is the html file for the Information Table:
When I click on it, the report itself looks like a convenient table format:
It’s here that you can see the holdings as of the end of the period for Berkshire Hathaway. I’m not going to copy this into Excel but instead, I am going to use the link itself.
Setting up the Power Query link
Back in Excel, I’m going to set up a connection by going to the Data tab and clicking From Web in the Get & Transform Data section. This will prompt me for a URL, where I will enter the link from the SEC website.
Upon clicking OK, the Navigator box shows up, where I have multiple tables to choose from:
Table 2 is the one that I want as that is the table that has the company names listed and all the other holding details. Since I want to make changes to the data, I’ll click on Transform Data rather than just loading it directly into Excel. Here’s how the table looks:
There are a few things right off the bat that I’m going to fix here:
The headers are not at the top.
The holdings need to be grouped so that I see the total per company.
To reference the columns easier, it’s important that the headings are correct. First up, I’ll remove the first two rows by clicking on the Remove Rows option in the Home tab in Power Query:
Removing the first two will get the names closer to the top, but the header names remain generic:
To fix this, I’ll click the option to Use First Row as Headers:
Now the first issue is fixed:
To group the companies, I’ll use the Group By option in Power Query:
On the next screen, I’ll group by issuer (i.e. the name of the company’s shares that are held), set the new column so that it is called ‘Total Shares’, and sum the PRN Amount (shares held):
Now I get a summary of all the shares that are held when grouped by company:
Now I can load this into Excel and have a list of the most recent Berkshire Hathaway holdings.
Comparing to a previous period
Next, I’ll compare the change in position from a previous period. Let’s say I want to go back a year. I can copy this query and just change the source so that it looks at this link from a year ago.
I’ve re-named the queries 2021-09-30 and 2020-09-30 (you can do this simply by right-clicking on the query name). To compare the two periods, what I’m going to do is merge the two queries. In the Merge options, make sure to select a Full Outer join so that all the rows are included. This ensures that you aren’t including just those companies that Berkshire Hathaway still owns shares of.
Next, I’ll expand the table to pull in the company name and the shares from the previous report:
This will give me the same values from both tables. One of the things you’ll notice is because of the full outer join, some companies and share values show as null because they don’t exist on both reports:
To fix this, I’m going to create a Conditional Column, which you can select from the Add Column tab. The rule I’m going to set up is as follows: if the value in one issuer/name column is null, take the other column’s value. Here’s how it looks:
For the change in shares, I’ll need to do a calculation that takes the current holdings and subtracts the previous holdings. But before that, I need to convert the null values where the share numbers are to 0. To do this, select those columns and on the Home tab select the option to Replace Values
Now I can create a Custom Column to calculate the change in shares:
Here’s my updated table after removing the other columns:
All that’s left is to load the data in Excel. Here’s what the end result looks like after applying some formatting and sorting the values in descending order:
If you liked this post on How to Track Warren Buffett’s Portfolio Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In Excel, there are multiple different stock charts you can create. All you need is some combination of the date, opening price, high price, low price, closing price, and volume to generate what you need. In this post, I’ll show you how you can utilize Power Query to pull that data in and transform it, without having to apply any manual changes to it every time. For an overview, you can check out this post on How to Get Stock Quotes From Yahoo Finance Using Power Query. I’m not going to repeat those steps and will assume that you’re familiar with that process.
In this example, I’ve downloaded the stock prices for Apple (NASDAQ:AAPL) for the month of October 2021:
This already has all the data that is needed to create the four types of stock charts in Excel:
High-Low-Close
Open-High-Low-Close
Volume-High-Low-Close
Volume-Open-High-Low-Close
The key to making these different charts is just ensuring that you’ve got the correct fields in your download, and in the right order. For the High-Low-Close chart, you only need three fields to generate the following chart:
If you’re creating the open-high-low-close chart, all you need is to add the open field to the data set:
And for the volume-high-low-close chart, it’s just the volume instead of the open that goes at the start, and then you get something that looks like this:
The last chart includes both the volume and the open before the high, low, and close values:
These charts are fairly straightforward to generate once your data is in the right order. But rather than moving around different fields, you can make all the changes within Power Query so that right when your data loads, it’s in the correct format.
Using Power Query to adjust your download
To modify an existing query, go to the Data tab and select Queries & Connections. Off to the right, you should see your query, where you can right-click and Edit it:
The first thing I’ll edit is the date range. In my earlier post, I just downloaded the full year of data. But if I want to filter only for October, then I can click on the drop-down for the Date field and select Date Filters to filter Between a range of dates.
Using the calendar icon, I can specify the range of dates I want to include in my download:
Next, if I want to just include the data for the most basic chart, the high-low-close chart, I’ll right-click on the Open, Adj Close, and Volume columns to remove them. Then, I’m left with the following:
Now, if I were to load the data in Excel, I would already have all the columns I need to create the chart:
Note: if you want to get rid of the gap in dates on the chart, click on Format Axis and for the Axis Type, select Text axis:
This prevents Excel from trying to fill in any missing dates from your data set Another thing you may want to do is format the date field so that it is a custom format showing MMM DD so that it saves space:
Now, let’s go back into Power Query and this time create the more complex download, for the volume-open-high-low-close chart. I’ll start by removing the last step I applied which removed more columns than I need for this current download. To remove any steps in Power Query, click on the ‘X’ next to it:
In this example, it’s just the Removed Columns step I will eliminate. The only column I need to remove from the download this time around is the Adj Close. So that’s what I’ll do, right-click and remove that column. However, my table still is not in the correct order:
The Volume column needs to go before the Open column. This is as easy as just dragging the column and putting it in front:
Now that it’s in the right order, I can load and close this into Excel. And now, the volume-open-high-low-close chart can easily generate:
Suppose I want to adjust my chart to include data from September as well. Again, I can go back to edit my query. This time, I’ll select the gear icon next to the Filtered Rows step:
There, I can just modify my date range using the calendar:
Now, re-loading the data into Excel automatically updates my chart with a simple refresh:
The beauty of this is this query will update with new data and your chart will also update, taking out the manual steps of having to make any changes yourself. And if you incorporate named ranges like in my post covering pulling stock prices, then the data will easily refresh based on the variables that you’ve entered.
If you liked this post on How to Create Stock Charts in Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Excel can be a useful tool in helping you make investment decisions. And in this post, I’ll use it to show you five excellent dividend stocks that you can buy on the Toronto Stock Exchange (TSX) that can help generate some strong recurring income for your portfolio for many years. Dividend stocks are great options to put into a tax-free savings account (TFSA), where you can earn recurring income without incurring taxes.
1. Fortis (TSX:FTS)(NYSE:FTS)
Fortis is a top utility company in Canada that also has operations in other countries. What’s great about the utility business is that it generates lots of recurring income; Fortis doesn’t need to constantly seek out customers to sell its services to, they are essential for millions of families. A great example of that consistency can be seen through its revenue and profit over the years:
You can see that even amid the pandemic, Fortis’ revenue actually increased. And its profits were in line with previous years. That’s a big part of the reason why the company can afford to not just consistently pay a dividend but also grow it over the years. As the business grows and expands to its operations, the company will have a greater pool of customers to collect revenue from.
From an annual dividend payment of $1.16 in 2010, Fortis has increased its payouts to $2.05 in 2021. That averages out to a compounded annual growth rate (CAGR) of 5.86%. If Fortis were to continue raising its dividend by that rate, then by 2025, it will be paying an estimated $2.58 per share:
What does that mean for an investor? If you invest in Fortis today, the stock pays a yield of approximately 3.9%. So on a $10,000 investment, you would be collecting roughly $390 in dividends for the year (based on the company’s current quarterly payment of $0.535). But by 2025, if the dividend is up to $2.58 per share, then you would be collecting $469 annually. That would mean you’re earning just under 4.7% in dividends on your initial investment of $10,000.
2. Canadian Utilities Limited (TSX:CU)
Another utility stock that’s on this list is Canadian Utilities. Like Fortis, this is another solid company that regularly posts strong numbers. This time, I’ll use the company’s cash flow to illustrate why Canadian Utilities is a reliable dividend-paying investment. Cash flow is arguably more important than just accounting income since it only involves cash and excludes expenses such as amortization.
Over the years, Canadian Utilities has generally had a safe buffer between what it has brought in from its day-to-day operations and the amount of dividends it has paid out to its shareholders. This is important because it confirms, regardless of what accounting income says, the business is in good shape to continue making dividend payments.
Like Fortis, Canadian Utilities has been one of the most reliable dividend growth stocks in Canada. From annual dividend payments of $0.81 in 2011, they have more than doubled to $1.76 today, averaging a CAGR of 8.13% during that time. For investors, that means the impressive 5% dividend yield today could eventually lead to you collecting more than 6.8% of your investment in dividends by 2025:
3. Canadian Imperial Bank of Commerce (TSX:CM)(NYSE:CM)
One type of dividend stock that’s always a popular option for income investors is a bank stock. These are businesses that print money and make terrific margins simply because the rate they charge loans out is higher than what they pay depositors. And the higher the interest rate is, the more of an opportunity there is for them to profit from more of a spread. That’s why with interest rates potentially on the rise as early as next year, CIBC and other bank stocks could be in for great years.
A simple way to illustrate the company’s strength is through its profit margins. Here’s how it has fared over the years:
Prior to the pandemic, the bank was consistently generating margins of around 27% to 29%. With the drop in interest rates in 2020 plus greater credit reserves needed to prepare for a potentially gloomier period amid the pandemic, the bank’s margins have suffered. But in the future, these margins should recover back to their pre-pandemic highs.
CIBC hasn’t raised its dividend since early 2020 but now that the bank’s profitability should improve, it’s likely that it will resume making more regular increases to its payouts. At close to 4%, the yield is reasonably high and over the past decade, CIBC has increased its dividend by a CAGR of 5.1% — that’s even with factoring in the recent slowdown. If the bank were to raise its dividend at that rate moving forward, here’s how much recurring income you could expect to collect:
4. Enbridge (TSX:ENB)(NYSE:ENB)
The highest-yielding stock on this list is pipeline giant Enbridge, paying its investors 6.3%. Although conditions in the oil and gas industry haven’t been great in recent years as low oil prices have squeezed profits for businesses, Enbridge has become more efficient and has benefitted from stronger margins:
Enbridge is an example where looking simply at net income could give you a distorted picture of the company’s ability to pay a dividend. With high fixed costs and depreciation expenses, a look at its payout ratio as a percentage of net income can be very misleading. Over the trailing 12 months, here’s how Enbridge’s payout ratio looks like when you are comparing it as a percentage of net income and as a percentage of operating cash flow:
The company has increased its dividend by a CAGR of 10% over the past 26 years. If it were to continue to raise its dividend by that rate, then here’s how long it would take you to earn 10% on your original investment every year, just in dividends:
5. Telus (TSX:T)(NYSE:TU)
Telecom giant Telus is another safe dividend stock for Canadian investors. The business generates solid gross margins of around 60% of revenue and without significant expenses elsewhere, the company normally nets operating margins of 15% or better. Its strong position in the industry makes it likely that these percentages likely won’t change significantly in the future:
One of the challenges, with Telus, however, is in predicting the rate of dividend growth. Although it is a safe bet that it will continue raising its payouts, its growth rate has varied considerably in the past 10 years:
In some years, it has been as high as 12% while in others it has been barely above 5%. Today, the stock yields just over 4.4%, which is a fairly high payout. Here’s a look at how much you could be earning on your initial investment, given the possible variation in dividend growth rates:
In a best-case scenario, you could be earning more than 7% while under a more conservative estimate, you could be collecting approximately 5.4% by 2025.
In this post, I’ll show you how you can calculate stock returns using Google Sheets. However, you can use a similar approach in Excel by using the STOCKHISTORYFUNCTION.
First thing’s first — let’s pull in the historical data
For this example, I’m going to pull in the S&P 500’s historical values to see how the index has performed both in the past 12 months and over the course of several years.
To do that in Google Sheets, I’m going to use the GOOGLEFINANCE function which allows me to pull in historical prices. To get the values from the S&P 500, the ticker symbol I’m going to use is ‘.INX’ and to get the last year of data, I’m going to set my start date equal to TODAY()-365 and my end date will be TODAY(). Here’s the full formula:
Once you’ve got the data loaded, then what you’ll want to do is enter the dates that you need values for. In this example, I’m going to use the last day of every month. For this, I can use the EOMONTH function. It takes two arguments: the start_date and the number of months. If I want the current month-end date, then I just set the second argument (months) to zero. As for start date, that can just be any date that falls within the month, which I can enclose within a DATE function. Here’s how the formula would look if I want the last day of September 2021:
=EOMONTH(date(2021,9,1),0)
But since I need to adjust this so that I can copy the formula down and have it automatically adjust, I am going to use the ROW function, which will return the current row number. Since I want the values to be increasingly negative as I copy down the formula (e.g. the current month should be 0, the following one -1, then -2, and so on), I will multiply this by a factor of negative 1 and add 1 to the total (to ensure the first value start at zero):
ROW(A1)*-1+1
That replaces the zero value from the earlier formula:
=EOMONTH(date(2021,9,1),ROW(A1)*-1+1)
And now, I can easily copy this formula down and my month-end dates will populate without requiring me to make any manual adjustments along the way:
Next, I’ll do a lookup to get the values. And that’s as simple as a VLOOKUP on my dates, which are in column A with the corresponding values in column B. If you use weekly dates, then be careful not to set the last argument in the VLOOKUP function to false because you’ll end up with errors as the weekly values won’t always fall neatly on the end of the month. Instead, leave the last argument blank or set it to TRUE so that it finds the closest match. Here’s what that looks like:
All that’s left at this point is to now just calculate the change in value. I can take the new value, divide it by the previous period’s value, and subtract one from it. This will give me a percent change:
If I wanted to determine the cumulative % change since my first month-end date, then the old value would always remain the same — it would be the first date in the series. By freezing that cell, I can calculate the cumulative % change:
If you wanted to pull in the returns by year, you can do the same thing. All that changes is that instead of pulling in the month-end dates you will use the year-end dates. The main difference here is in calculating the different dates. Rather than multiplying by a factor of -1, you’ll need to use -12. And the starting date should be Dec. 1. Here’s how my formula looks like:
=EOMONTH(date(2020,12,1),ROW(A1)*-12+12)
And when I copy that down, it will automatically adjust for each previous year:
The one thing you may notice in Google Sheets is that the GOOGLEFINANCE function returns a timestamp for the date. Each day ends at 16:00:00. This can create some unintended results. For example, using the VLOOKUP function, if I use the date 12/31/2020, because it looks for an approximate match, it will actually return the value from 12/30/2020. Unless you add the timestamp, an exact match won’t work. And since a date with no time will by default by 0:00, the lookup of 12/31/2020 16:00:00 won’t be a match. One way to get around this is just to use a different date. Rather than using the EMONTH function, I can just adjust the date by reducing the year by 1. This is the formula I can use if instead I want to get the first day of the year:
=DATE(2021-ROW(A1)+1,1,1)
Using the ROW function again can allow me to automatically adjust the year. Here is the updated table:
If you liked this post on Using Tags in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.
Step 1: Compiling the data
You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.
To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:
=GOOGLEFINANCE(“.DJI”,”price”)
And to calculate the percentage change:
=GOOGLEFINANCE(“.DJI”,”changepct”)/100
For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”
For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:
Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:
In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:
Now, it’s time to pull all this data into Excel.
Step 2: Loading the data into Excel using Power Query
To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:
Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:
On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:
To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:
Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.
Repeat these steps for the other Google Sheets tab.
In addition, I’m also going to load data from a few other sources:
Top 100 Gainers on Yahoo Finance: https://finance.yahoo.com/gainers/?offset=0&count=100
Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100
Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/
The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.
Step 3: Creating the dashboard
In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.
To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.
First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:
To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):
=E$6<0
This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:
For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:
For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.
To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:
Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:
To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:
These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.
Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:
Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.
Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.
In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):
That will create a new column:
In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.
After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:
Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.
The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:
To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:
Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:
SUBSTITUTE([@Volume],”B”,””)
In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:
SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)
I multiply this by the first part of the formula, and my numerator is as follows:
For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:
The -1 at the end is to put the change in a percentage of less than 100%.
Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:
#,##0%;[Red]#,##0%
The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.
Here’s how this section looks as part of my dashboard:
And here’s a snapshot of the dashboard as a whole.
One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.
Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:
Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.
If you liked this post on Creating a Stock Market Dashboard in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In this post, I’ll show you how you can import a company’s financial statements into Excel using Power Query. Previously, I’ve covered how to get stock prices from both Yahoo Finance and Google Sheets. But to get financial statement information, I’m going to use a different source: wsj.com. The reason being, is it’s in an easy format to export and that makes the import process very easy for Power Query.
Downloading the data
I’m going to use Walmart’s financials for this example. And if you navigate to the following URL, you will get a summary of Walmart’s quarterly financial statements:
What’s convenient about this URL is that it contains both the ticker, the statement type, and indicates that the financials are quarterly. That makes it easy to alter in case you wanted to look for annual statements or a balance sheet rather than an income statement. Just changing the URL will get you to the right page. The above link is what I’m going to use for this example.
To load the data into Power Query, go to the Data tab and click on From Web:
Then, paste the URL in the following box:
After clicking OK, you can select which table to import. In this case, it’s going to be Table 0:
Next, press the Transform Data button to make changes before it gets imported. I’ll start with removing the column at the very end, showing the trend, as it doesn’t contain any information. To remove it, right-click on the header and click Remove:
I’m also going to remove the Changed Type step, which automatically changes the data types. To get rid of the step, click on the X next to the step:
This is important because since the header names change based on the quarter, it isn’t going to be helpful to have this step since it looks for hardcoded values. An optional step you could take is to Demote Headers so that the header names are generic and not tied to a specific quarter. However, this isn’t necessary if you remove the Changed Type step. For more information on changing header names, refer to this post.
Once you’re done making changes, click on Close & Load in the top-left corner, and then your data will load into a sheet.
The download will work just fine right now. However, let’s also make the file a bit more versatile in case you want to quickly change the ticker symbol.
Setting up the variables
First up, I’ll create a named range for the ticker symbol, called ‘Ticker’ :
I’ll now go back into the query editor to account for this named range. To edit a query, go into the Data tab, click on Queries and Connections, and then off to the right you should see your queries. Right-click edit on the one you want to adjust:
Then, click on the Advanced Editor button near the top of the Power Query window:
I’m going to add the Ticker variable under the let section as follows:
Note that Power Query is case-sensitive and you will get an error if what you’ve entered doesn’t match exactly what you’ve set as your named range. Also, make sure to add a comma at the end.
I will also need to adjust the Source variable so that it uses the Ticker variable:
The key thing here is to break up the part of the URL that mentions WMT and replace it with the named range. Here’s what the code looks like within the Advanced Editor:
Now, you can Close & Load back into the worksheet. To test the named range, what you can do is replace the ticker value from WMT to AMZN, and if it works correctly, it should load Amazon’s income statement instead. After changing the ticker symbol, remember to press the Refresh All button under the Data tab:
If it works, you should see a whole new set of data populate on your spreadsheet:
If you liked this post on How to Import Financial Statements Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
