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
Learn more with Excel CPE
Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!
Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:
- Excel: Technical Analysis Trading Strategies
- Excel: Enterprise Risk Management
- Excel: Magic with Excel
- Excel: Solve Hard Problems in Corporate Finance
- Excel Metrics: Best Practices
- Python for Excel Users: A Gentle Introduction
These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!
Unlock unlimited CPE with a Prime Subscription
Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry!
With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits.
