If you have OFFICE365, you should now (or soon) have access to the amazing STOCKHISTORY function. This function can return past data on stocks or currencies including opening price, closing price, high price, low price and trade volume. Data can be returned on a daily, weekly or monthly basis. The syntax of the STOCKHISTORY function is:
STOCKHISTORY (ticker symbol, start date, end date, frequency, headers, properties)
- Ticker symbol is the stock ticker symbol. For example, for GameStop, the ticker symbol is GME. For Bitcoin, the ticker symbol is BTC/USD. For the Euro exchange range, the ticker symbol is EUR/USD.
- Start date marks the first date for which data is returned.
- End date marks the last date for which data is returned. If you use =TODAY() as your end date, the STOCKHISTORY function will automatically update to include the most recent data!
- For frequency, the argument 0 returns daily data, the argument 1 returns weekly data, and the argument 2 returns monthly data.
- For headers, the argument 0 returns no column header, the argument 1 returns a column header, and the argument 2 returns a column header with the symbol for the investment one row above the column header.
- Properties specifies which of the following six pieces of data you want returned, outlined below.
0 = Date
1 = Closing price
2 = Opening price
3 = High price
4 = Low price
5 = Trade volume
GameStop Mania!
Right click and copy this link into a new browser to open the Excel workbook and follow along. On the worksheet GameStop, we used the STOCKHISTORY function to return daily data for the period 1/2/2021 through 3/14/2021.
To return the data (a subset of all returned data) shown in Figure 1, in cell E4, we entered the formula =STOCKHISTORY("gme", D2,D3,0,2,0,1,2,3,4,5). Note cell D3 contains the =TODAY() function, so our daily data will always include the most recent trading day. The last six arguments 0-5 ensure that for each day, all available daily data is returned.
Figure 1: GameStop 2021 data
In Figure 2, we graphed GameStop’s daily closing price and trade volume. We selected the data in columns E and F, and after holding down the Control key, we selected the data in column J. Next, we selected the first line chart option from the “Insert” tab. Since trade volume and closing price differ greatly, we right clicked on the “Volume” series. From the “Format Data” series, we created a secondary axis. The resulting chart is shown in Figure 2.
Figure 2: GameStop stock price and volume over time
Figure 2 shows the GameStop price mania (and high trade volume) that roiled the markets in early 2021.
Monthly Bitcoin Prices
As shown in Figure 3, the formula = STOCKHISTORY(H3,H4,H5,2,1,0,1) in cell K4 of the worksheet “Bitcoin and FX” returns monthly Bitcoin closing prices for the period January-October 2020.
Figure 3: Monthly Bitcoin closing prices
Weekly Euro/Dollars Exchange rates
As shown in Figure 4 below, entering the formula
= STOCKHISTORY(G14,H16,H17,1,1,0,1,3,4)
in cell J16 returns the weekly close, high and low exchange rates for Euros. For example, at the close of the 3/8/2021 week, a Euro was worth $1.20.
Figure 4: Euro-dollar exchange rates
I hope you will find the STOCKHISTORY function enables you to better track your personal (or your company’s) investments. Next month, we will explore Office 365’s amazing new data types which enable you to easily download many important pieces of data into Excel.
Keep visiting the Becker blog for more of my best Excel tips.
