ICAEW.com works better with JavaScript enabled.

Excel Tip of the Week #420 - STOCKHISTORY and exchange rates

Author: David Lyford-Tilley

Published: 16 Nov 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post in which we’re examining the handy live financial data function STOCKHISTORY, and in particular looking at how to adapt the basic function for computing exchange rates.

This was briefly mentioned in TOTW #384 on data types, which it’s related to, so you might want to read that article first.

How does STOCKHISTORY work?

The STOCKHISTORY function is only available in Excel 365, and relies on the dynamic array spilling behaviour that entails – so you won’t be able to use this function in older versions. It can be used to return a table of prices for stocks or currency pairs over time.

Here’s the basic syntax:

=STOCKHISTORY(stock, start date, end date, interval, headers, properties 1, properties 2, …)

Stock is the ISO-format stock ticker for the stock you want to identify – e.g. MSFT or XNAS:MSFT – or the ISO currency codes for the two exchange rates – e.g. GBPUSD.

Start date is the date you want to start your table at (or just the date you want the value for if you are only returning one result).

End date is the end of the range of dates in the table

Interval is an option setting – 0 or blank for daily figures, 1 for weekly, or 2 for monthly

Headers is another option setting – 0 to excludes headers from your table, 1 or blank to include headers, and 2 to show both the instrument identifier and headers.

Properties is the final input to the function, and you can include several; this allows you to decide which columns are included in the table. The options are:

0 – The date of the data point
1 – The close price
2 – The open price
3 – The high price
4 – The low price
5 – The trade volume

You can include any number of these in whatever order you like; if excluded the default table includes the date and close price.

All this is fairly theoretical, so let’s look at an example, which returns weekly close prices for a given stock:

Screenshot of Excel spreadsheet

Note that this is a single function in one cell, which spills results into an array. Also note that any workbook containing one or more STOCKHISTORY functions will automatically also display a disclaimer from Microsoft each time it is opened, which you can see at the top here.

This function is very handy for making variable charts – but it does require a little set up. You can’t set a spilled dynamic array directly as the source of a chart, but you can set it as the definition of a named range, and then set the named range as the source. We also want to lose the headers for this one:

Screenshot of Excel spreadsheet

There are two defined names here:

StockChartSource: =INDEX('Stock tracker'!$B$6#,0,2)
StockChartLabels: =INDEX('Stock tracker'!$B$6#,0,1)

These are then set as the data and axis labels for the chart (you also have to specify which sheet the range names belong to). This way the chart will expand and contract as we amend the formula.

Making an exchange rate function with STOCKHISTORY

You can also use STOCKHISTORY to compute an exchange rate for use within a single-cell function. Let’s look at how we can build this up. We have the following:

Screenshot of Excel spreadsheet

We can start with just a basic STOCKHISTORY function:

Screenshot of Excel spreadsheet

Note that we have used & to conjoin the currency code from column B with the text value “GBP”, as our home currency. As you can see, this shows us the rate for the given date in a 2x2 table.

We can then specify that we only want the close amount and no headers:

Screenshot of Excel spreadsheet

That gets us our rate – all that remains is to convert the local amount and round off:

Screenshot of Excel spreadsheet

One note – using the close rate will actually lead to an error if the transaction date is today’s date. In the above we have used the open rate instead; alternatively you can either use an IF to pick the open rate if the date is today, or just leave the error – it will be fixed once the market closes and a close rate is available.

You can check out both of these examples in the attached file .

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.