STOCKHISTORY Function

Introduction

In June 2020 or thereabouts, Microsoft announced the STOCKHISTORY() Function. I read this morning that the function is now in general release for all Microsoft 365 subscribers. This page provides good insights and examples of what the function is and how to use it.

What does STOCKHISTORY() do?

The STOCKHISTORY() Function enables us to enter the name of a stock or share currently being traded on something like 50 exchanges around the world and retrieve the history of those prices for a day, a week, a month, years. There are very few rules, which is another way of saying the function really is easy to use, although at first sight it looks complicated:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

Please note, all of the parameters enclosed by square brackets are optional. That means, of the eleven parameters listed, only two of them are required: stock name and start date. 

The Parameters

  • Stock: Enter a ticker symbol in double quotes (eg “MSFT”) or a reference to a cell containing the Stocks data type. You can refer to a specific exchange by entering a 4 character ISO market identifier code (MIC), followed by a colon, followed by the ticker symbol (eg “XNAS:MSFT”).
  • start_date: The earliest date for which data is retrieved. This can be a reference to a cell containing a date or a date typed in as text (ie within double quotes).  Note that if the [interval] is not 0 (daily), the first data point may be earlier than the start_date provided – it will be the first date of the period requested.
  • [end_date]: The latest date for which data will be retrieved. The default is start_date.
  • [interval]: Specifies the interval each data value represents as follows: 0 = daily, 1 = weekly, 2 = monthly. The default is 0.
  • [headers]: Specifies whether to display headings as follows: 0 = no headers, 1 = show headers, 2 = show instrument identifier and headers. The default is 1 (e, show headers).  When included, headers are rows of text that are part of the array returned from the function.
  • [property0 – propert5]: The columns that are retrieved for each stock as follows: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, and 5 = Volume. If any of them is present, only the indicated columns are returned in the order provided. The default is 0,1 (ie Date and Close).

By the way, if you don't know the stock ticker for your chosen stock, just enter the name of the company in a cell in your worksheet and use the Data ... Stock Type to find it.

An Example

Here is a basic example of how to use the function:


All I did was to put the ticker for amazon.com in cell A5 and refer to that in the STOCKHISTORY() function, enter the dates on which I would like my history to begin and end, the interval over which I would like to see my history, 2 = monthly in this case and, finally, I chose 1 to say that I would like to have column headers at the top of my history, in B5:C5 in this case.

That is it, Excel the Date and Closing Price as defaults even though I did not choose them as property 0 and property 1 respectively.

Once I pressed Enter, cell B5 showed me the #BUSY! message to let me know it was dealing with my request. After a few seconds, I got my data.

What happens if we don't enter a closing date, for which I entered TODAY()? This happens:


If we don't enter a closing date, we just receive the history for the starting date. Again, simple!

You don't have to use TODAY() as your closing date, any acceptable date will do and do notice that dates are entered as text, hence the "" around them.

Extended Example

In my workbook, downloadable from the link at the end of this page, you will see that I have used all parameters in my analysis of several companies, starting with amazon.com. My output from using STOCKHISTORY() is as follows:

=STOCKHISTORY(A5,TODAY()-366,TODAY(),0,1,0,1,2,3,4,5)


You might have spotted that I have used Conditional Formatting to help me to find the largest values in some of the columns and I have created calculated columns, 
  • Change Open =C8/C7-1
  • Change Volume =F8/F7-1
  • Change Base Open =C8/C$7-1
  • Change Base Volume =F8/F$7-1
I created a series of graphs as well as programming a Correlation Matrix, the Table of Descriptive Statistics.


It is best if you download and refer to my Excel file to appreciate everything that I did.

Do notice that I said I had programmed the correlation matrix and table of descriptive statistics. The reason I did that is two fold:

  • If I update the STOCKHISTORY() parameters, these two tables will NOT update unless you program them as I did
  • Secondly, I created this worksheet and example to provide a template for myself and for anyone else. In that, all I need to do is to change the name of the stock I want to explore and press Enter and the STOCKHISTORY() table will update as will the graphs, the correlation matrix, the table of descriptive statistics and whatever else I have done!

Using the Template

In my file, I have used my template five times, for:

  1. amazon.com
  2. Microsoft
  3. Apple
  4. Alphabet (google)
  5. Gamestop ... I included this tock as it is fascinating and very topical as I write this page!

Take any of my template sheets and copy it then enter a stock ticker of your own in cell A5 of that sheet, press Enter and wait a few seconds. I have set the dates at TODAY()-366 and TODAY() BUT you might want to hard code your own dates and you do that in the function in cell A6 ... that is all you do!

A Second Template

On the worksheet stockhistory_DV, I have created a Data Visualisation drop down box that contains the names of a total of ten stocks, including the five above. I added

  • BP plc
  • Exxon Mobil
  • Walmart
  • Toshiba Corporation
  • easyJet plc

Again, accepting your need to change the dates and possibly to deselect some of the property parameters, your template will do what you want. Except, of course, I have given you the full template and deselecting parameters will mean that you will disable or wrongly enable the graphs, correlation matrix and so on. It's not the end of the world but you would have some work to do to change it! 

This is what might happen if you deselect some parameters ...


By the way, if you are studying this function intently as well as studying other aspects of Excel, it will stand you in good stead to update my template ...

Horizontal STOCKHISTORY() Report

I then took a completely different approach and rather than having a potentially very busy table reading from top to bottom, I created a horizontal report for all ten of my companies but using just column headings and one parameter: closing prices.

I set up my table of company names using the lists I had already prepared in the file and then in cells C5:C6 I created these two formulas

C5 =INDEX(TRANSPOSE(STOCKHISTORY(A6,TODAY()-366,TODAY(),1,0,0,1)),1) ... this shows the Date only

C6 =TRANSPOSE(STOCKHISTORY(A6,TODAY()-366,TODAY(),1,0,1)) ... this shows the Closing Prices only

To complete the table, I filled down the formula in C6 to C7:C15


I have included some additional detailed analysis on this worksheet that you should find interesting:









I have use Sparklines in column B and Concatenation in column C to create the summaries.

Summary Sheet

If I felt it necessary, I could also create a summary sheet on a separate worksheet to illustrate the outputs for my horizontal analysis. 

Conclusions

I do gather share price histories on a regular basis so this new function is ideal for someone like me and while I believe this page is a good quality and comprehensive introduction to the function, you will do well to experiment with it to learn more about it.


Excel file download



Duncan Williamson

30th January 2021








No comments:

Post a Comment

Subscribe to: Posts (Atom)