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.
If a stock you invested in dropped in price, it could be a good opportunity to buy more shares and bring your average down. You can use the average down calculator on this page to do a quick what-if calculation to determine how many more shares you would need to be. However, you can also use this template, which will allow you to run through the same scenarios within Excel.
This is how much money you have already invested into the stock.
Shares owned
The number of shares that you own.
Current share price
What the share price is.
Desired average price
What price you want to average down to.
Budget
How much money you can afford to invest.
Increment price by
This is for the sensitivity analysis and determines by how much you want it to move by. The default is set to $0.50.
Once you’ve entered that data, the rest of the template will populate. Here are the two scenarios that it will show you:
1. Getting to your desired average price
In this scenario, the template will show you how much to invest at different price points to get your average down to your desired average price. You will see up to 20 different data points to show you if the price continues to get lower, how many shares you will need to buy to reach the average price you are targeting.
And any scenarios that fall within your budget will be highlighted in green, and so will the corresponding chart:
If all the data points aren’t filled in or it looks like the chart doesn’t go all the way to the right, this is a sign you need to fix your Increment Price by value. Enter a smaller price increment and you’ll see more data points and a more complete chart.
2. How low you can get your average
The second scenario ignores the desired average price and simply tells you the different average prices you can average down to if you buy at the current price. This is good if you don’t have a specific average in mind and just want to see how low you might be able to go.
You’ll notice on the x-axis it refers to the average price rather than the share price in the earlier chart.
Please note that the template is locked down and this is to prevent overwriting formulas which could lead to errors in the calculations and the charts.
Download the file
You can download the file for free, from here. The free version is limited to five price points. On the full version, there are 20 different prices, no ads, and there are more scenarios:
If you liked this Average Down Calculator Template, 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.
A stock screener allows you to filter through stocks that meet your investment criteria. It can help you find undervalued stocks and great dividend investments. But sometimes it can be cumbersome to always go back to a website and re-apply filters, even if you save them. In this post, I’ll go over how you can populate a list of stock data into Excel and then run your own filters on it, and thus, creating a screener you can easily access from within your own spreadsheet.
Step 1: Populating the list
The one thing you’ll want to do before you can create the screener in Excel is to download an array of stock data from a database. Personally, I like using Barchart because it has lots of useful information on there and you can get a wide range of data, and it is easily downloadable into an Excel format. It lets you do five free downloads each day and you can download 1,000 rows at a time. That’s thousands of stocks you can add. Using that in conjunction with the STOCKHISTORY function, and you can create a pretty versatile template. After all, since data like earnings, dividends, and other fields won’t often change, downloading a snapshot from Barchart once a month or even less frequently shouldn’t be a big issue. You can obviously use other databases but I’m going to use a free example for the purpose of this post.
On Barchart, I’ve customized the fields I want to use for my downloads, and this allows me to re-use them again and make subsequent downloads easier. To keep it simple, I am going to download just the top 1,000 North American stocks based on market cap. This is what my download looks like in Excel:
Now that the data is loaded, the next step is to create the layout.
Step 2: Organizing the stock screener and setting up the fields
I find it most convenient to always put any inputs on a spreadsheet on the top of the page, and the results below. This way, you can freeze panes to make it easy to scroll through all the rows while seeing your selections.
To start, I will create a field for each major field I have downloaded. After formatting some of my values, this is how my screener looks thus far:
Off to the right, I’ve added a date field because I am going to utilize Excel’s STOCKHISTORY function to pull in the price. This will allow me to calculate the current price to earnings ratio without having to download it from the screener as that multiple will change every day based on the stock’s price.
When downloading so many stock prices, it may take a while for the formulas to update. But once they are loaded, then I can calculate the P/E ratio by just taking the stock price and dividing it by the earnings per share.
Step 3: Creating the formulas to evaluate the criteria
The part that will take the most time is to now evaluate each of the criteria to determine if a stock meets all of it and whether it should be included in the results. Rather than trying to do this in one large formula, I’m going to break this up into one formula per field. I’m going to name these fields exactly the same so that it is easy to reference them.
For the first criteria, Market Cap, my formula looks as follows:
D2 is where I have the dropdown for the > or < symbol and E2 is the value that I want to filter for market cap. C9 is the first row of data. My goal here is to evaluate to either a TRUE or FALSE value. I also divide the value in C9 by 1,000,000 just to make it easier to filter the market cap by millions.
For the % change calculations, I will do a similar calculation. Except this time I don’t need to divide by 1,000,000 and so it looks a lot simpler:
=IF(E3=””,TRUE,IF(D3=”>”,D9>E3,D9<E3))
D3 is my > or < dropdown while E3 is the percent change I am entering. Since I will enter a percentage here, I don’t need to make any special calculations. This is the same format that I will follow for the other fields.
Once I have set up all my calculations for the various criteria, I’m going to add one column that will check to see if the stock meets all of them. This is a simple formula where I can multiple all the values. A TRUE value will compute as 1 and a FALSE will be 0. And so even if there is one FALSE value, the entire result will return FALSE and not meet the criteria. The formula looks as follows:
The final step is a simple one but it’s also important to make this sheet work smoothly. Select anywhere on the data set and on the Insert tab, click on Table. Hit OK and now you should see Excel’s default table applied to your data.
The reason for converting this into a table is that now we can apply slicers to it. And really, only one is needed here. If you go to the Table Design tab, there is a button to Insert Slicer. Click on it and select the one for the field that checks all the other criteria. In my example, it is called Criteria Met.
After hiding all the criteria fields, changing some of the formatting and adding the slicer, this is now how my screener looks like:
The beauty of this stock screener is that by clicking on the TRUE button in the slicer, you are automatically refreshing the data in Excel and updating your filters based on the selections. All this is done without macros and it makes the screener easy to change with the press of a button.
You can download my completed template here. Please note that if you do not have STOCKHISTORY available on your version of Excel, some of the values will not populate.
If you liked this post on creating a stock screener 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.
There are a few different ways you can pull stock prices into Excel. You can use the new STOCKHISTORY function, pull data from Google Sheets which has a native stock function, or you can also use Power Query. In this post, Power Query is what I am going to focus on and show you how you can pull data right from Yahoo Finance. Of course, if you don’t want to do it yourself, I also have a template that is ready to use and download (at the bottom of the page).
Creating the query
For this example, let’s pull Apple’s stock history for the past month. To do this, we can simply go to the Yahoo Finance page that shows the stock’s recent price history, located here and select any interval, whether it is five days, one month, or three, it doesn’t matter.
To get the complete data set, I’m going to copy the actual CSV download link from that page, not simply the URL. That way, it is possible to pull a much wider range than the default of 100 days.
I’m going to use that link to set up the query. To create it, go into the Data tab, select the From Web button next to Get Data:
On the next page, you’ll be given a place to enter a URL, and this is where I am going to enter the download link from Yahoo Finance:
Click on OK and Power Query will connect to the web page. Next, you will see a preview of the data and if it looks okay, you can just click on the Load button:
Then, the data will load into your spreadsheet and it should look something like this:
If that’s all you need, you can stop here. The only downside is if you wanted to look at a different ticker or change the date range, you would need to get a new link, and update the query manually, which is not ideal at all. This can be automated and takes a little more effort but it can be done by adding some variables and making some tweaks to the query.
Setting up the variables
In Power Query, you can utilize named ranges. In this case, I’ll set them up for the ticker symbol, as well as the start and end dates. That way, I can pull up a stock’s history for a specific time frame. The three named ranges I’m going to create are called Ticker, StartDate, and EndDate which can be entered all in the same place:
For the dates to work on Yahoo Finance, they need to be converted to a timestamp. This is what that calculation looks like:
=(A1-DATE(1970,1,1))*60*60*24
Where A1 is the date. This is what the dates look like when converted into this format:
Those timestamps are needed for the Yahoo Finance URL to populate properly. These are the values that need to be tied to a named range.
Next, these ranges need to be coded into Power Query. To do this, click anywhere on the table that the query created, and you should now see a section for Query in the Ribbon and click on the Edit button:
That will launch the editor. From there, you will want to click on the Advanced Editor button:
Then, you’ll see how the query is coded:
You can see the source variable is where the URL goes. To insert a named range from the Excel document into this code, we need to use the following format:
To keep things simple, I kept the name of the variable the same as the named range within the Excel file. Here is what the editor looks like after adding in the variables for the ticker, start date, and end date:
The one thing that I still need to adjust is the source. This is a hardcoded URL and it needs to be more dynamic, utilizing the variables.
In this part, I’ll need to adjust the query carefully to ensure that it is generated correctly. I will put the ticker variable where the ticker should go, and put the start and end dates (in Unix format). This is an excerpt of how the updated source data looks like:
Note that for the start and end date named ranges, I included the = sign to ensure the variable is read as text.
Now that the source is changed, all you need to do is update the variables and click on the Refresh All button on the data tab, and the table will update based on what you have entered.
If you want to download my template, you can do so here.
If you liked this post on how to get stock quotes from Yahoo Finance 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.
Several weeks ago, I discovered that Excel had a new function called STOCKHISTORY. It’s able to pull stock prices and a great way to track stock prices and can help calculate returns. Excel does make it clear that it is not for trading purposes. However, it’s still a great way to stay on top of tracks and see how they’re performing. Below, I’ve created a template that will allow you to track stock prices and arrange them from best-to-worst.
Note that for this template to work, you need to have the STOCKHISTORY function on your computer, otherwise you’ll get nothing but errors. So your first step will be to check if it works on your file. Refer to the original post on the function as it will also explain how you can get it on your computer if you don’t already have it. If you’re running on old versions of Excel, you’re out of luck.
But for those that aren’t and that have access to the function, read on.
Entering the ranges that you want the macro to sort.
Let’s start with the first one, selecting stocks. I’ve already created three stock sections in this template, which you can of course change. Let’s look at one of them as an example:
The Start, End, and Return values are formulas. The only things you need to enter are the ticker symbols. Off to the left, shaded in light grey, I’ve also entered the code for the exchange. For the New York Stock Exchange, it’s XNYS, while the NASDAQ is XNAS. For a full list of the codes, refer to the original post on the STOCKHISTORY function. If it’s a popular stock that’s on one of the major exchanges, you may not need to enter it. I’ve included the exchange code for the sake of avoiding errors as it’s possible Excel might not know which ticker you’re looking for and select the wrong one.
You can extend the ranges to accommodate more tickers, you’ll just need to copy the formulas down in the Start, End, and Return sections.
Next: the date ranges.
Off to the right of the template, there’s a section where you can enter the start and end dates.
The template will adjust for weekends but not for holidays. If you see a #VALUE! error in the values, that likely means there’s an issue with the date, so you’ll just need to change one of the dates to ensure it doesn’t fall on a holiday.
Lastly: the ranges to sort.
To the right of the dates, there’s another area where you can enter which cells to sort:
Cells E8, K8, and Q8 on this template are where my ‘RETURN’ headers are located, and where the percentages are. If you add sections or modify this template, you’ll need to update the cells to sort. When you update the start or end dates, the template won’t automatically re-sort until you click on this button:
If you get an error on the re-sort button, make sure you check which cells are in the Cells to Sort area and ensure that they’re correct.
#CONNECT! errors
One thing you may run into on this template are #CONNECT errors. I’ve noticed this happens once you start adding too many ticker symbols. Sometimes it’s hit or miss and you’ll get all the prices updated, but if you’re planning to list every ticker out there, just be forewarned that you might run into issues here. It’s a separate error from the #VALUE! error and one that can’t be fixed through the template, without removing some ticker symbols, anyway.
If you liked this post on the StockHistory Template, 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.
For a while, one of the big advantages Google Sheets had over Excel was the ability to pull stock quotes easily. But that’s no longer the case as there is a new function in Excel that allows you to pull in stock price history. Below, I’ll cover how to use the StockHistory function.
How the function works
The function itself is fairly simple and requires just two arguments at a minimum, and that’s the stock ticker and the start date. By default, the function will return the closing prices from the start date until today. For instance, if I want to pull Tesla’s share price since the start of the year, this is what my formula will look like:
=STOCKHISTORY(“TSLA”,”2020-01-01″)
The formula will then generate an array. Here’s a portion of what it looks like:
If you want to pull just the most recent share price, here’s what you can do:
=STOCKHISTORY(“TSLA”,WORKDAY(TODAY(),-1))
Using the WORKDAY formula you can ensure that you’re going back one business day. You may need to adjust this if you’re on a weekend but basically you just need to manipulate the date to make this work. Note that this doesn’t appear to give you the current day’s close. When I ran this on a Friday, the most recent closing price it returned was from Thursday’s close. It’s clear this function’s intended for historical data rather than live or even delayed stock prices.
If you want to specify an end date for your data, you can enter a date in the third argument, right after the start date.
The function gives you many options, including which data points you want to pull in and what intervals you want. You can pull prices on a monthly or weekly basis by selecting either a 0 (daily), 1 (weekly), or 2 (monthly) for the interval argument. Here’s how I’d pull monthly prices for Tesla:
=STOCKHISTORY(“TSLA”,”2020-01-01″,,2)
It’s important to note that these aren’t monthly averages, they’re just the stock prices as of the end of the specified month. Although the date for the first entry suggests January 1 (the markets weren’t open that day), that’s actually the January 31 closing price.
You can choose whether you want to see the headers and you can also add more fields, including the opening price, the high, the low, and the volume. You can even determine if you want to even see the date (although that’s probably not a good idea when you’re looking at historical data).
It’s easy to make a template with this function since it populates the data for you. Using variables for the ticker, the start date, and the end date, I can quickly set up a sheet that’s easily updatable:
The only formula that I enter is the one cell for the STOCKHISTORY function:
=STOCKHISTORY(C2,C3,C4,0,1,0,1,2,3,4,5)
Where C2, C3, and C4 refer to the stock, start, and end dates. The numbers 1 through 5 are needed to ensure that all the fields are extracted.
If you want more details about this function including the different arguments, you can check out Microsoft’s official page for this function.
How can I get other (non-US) tickers?
One of the things you’ll notice from the above examples is that I didn’t enter any prefix for the stock ticker. The StockHistory function knew I was looking for Tesla’s stock price. However, if you want to pull data from other exchanges, including those outside the U.S. markets, you’ll need to add a prefix to make sure that you’re getting the right quote. And since the function won’t actually return the company name, you need to make sure you’re entering the ticker correctly into the function.
Refer to this link for all the different market identifiers. For instance, if I wanted to pull the share price of Air Canada, which trades on the Toronto Stock Exchange, I’d need to enter the ticker as follows:
XTSE:AC
In most cases, it looks as though it’s just an X before the exchange’s usual prefix but you’ll want to double check to make sure.
Why you may not find the StockHistory function on your version of Excel
Since the function’s in beta, StockHistory is not available for most users. You can, however, sign up for Microsoft’s Office Insider program which will give you access to functions while they’re in beta. To join the program, follow the steps outlined here.
If you liked this post on How to Use the New Stock History Function 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.
Scroll further down if you would like to see details as to how this calculator works and a description of it.
There is a new version of this calculator available here (mobile-friendly) that will make calculations at multiple price points at once (use the new interval field to specify how much in price you want to jump by). It will also let you know how low you can average down at the current share price.
If you invest in stocks and want to know how much it would cost you to average down, this calculator will help you do just that. Averaging down is a great way to take advantage of a stock that’s dipped in value and that you’re confident won’t stay there. By purchasing more shares of a stock at a lower price, you’re bringing down the average cost of your total investment. And that means you’ll need the stock to rise to a lower price than before to turn a profit. Or if you’re already in the black, then you can put yourself in a great position to increase those profits.
How the average down calculator works
To use this calculator, you’ll need to enter the total dollars that you’ve invested in a stock, how many shares of it you own, what the current price of the stock is today (or the price that you plan to buy it at), as well as what price you want to average down to. Then, click on the Calculate button. The calculator will then tell you how many shares you’ll need to buy and how much it will cost you in order for you to get to that average.
Note that since you can’t average down below what the current share price is, you’ll have to make sure that your desired average price is higher than where the stock is today. Here is the calculator:
If you liked this free average down calculator, 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.
