Buzz
Microsoft has recently introduced the STOCKHISTORY function in Excel. This innovative Excel function is designed based on the flexibility of dynamic arrays and the ability to gather information from cloud-based Stocks data by providing access to historical data.
Concepts and Usage of STOCKHISTORY Function in Excel
Unlocking the Potential of STOCKHISTORY in Excel
The syntax for the STOCKHISTORY function in Excel is as follows:
STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])
Where:
Stock: serves as the identifying code for the targeted exchange; it could be a stock symbol or Stocks data type.
start_date: marks the commencement date for receiving information.
Example of STOCKHISTORY Function in Excel
Example 1
For the following Excel function:
=STOCKHISTORY('MSFT', '6/1/2020', '6/5/2020')
In this example, we use the stock symbol MSFT. The start_date and end_date inputs are converted by Excel to the appropriate date format, similar to how the DATEVALUE function operates.
Note: Ensure that text values are enclosed in double quotes within the Excel function input.
Example 2: Selecting a Specific Exchange
When using a stock code as an input without specifying any specific exchange, the default exchange values are assigned as XNAS (NASDAQ).
There are 2 ways to target a specific exchange on Excel:
- Option 1: Use the Market Identifier Code
The first method is to use the Market Identifier Code + colon ':' + stock code to retrieve data from a specific exchange.
=STOCKHISTORY('XMIL:MSFT', TODAY()-7, TODAY())
In this example, Mytour specifies the stock code MSFT on the Borsa Italiana exchange ('XMIL:MSFT') and requests data for the last 7 trading days using the TODAY() function. This function updates when the date changes.
Note: Stock prices are calculated in Euros.
- Option 2: Use the Stocks data type
The second approach involves using a cell containing the Stock data type as a reference in the function, similar to referencing any other cell.
=STOCKHISTORY(B1, B3, B4)
In the example above, the Stock data type is used to fetch data from the Mexican stock exchange. Here, the cell containing the STOCKHISTORY function (B1) references the cells containing the dates (B3 and B4).
If you modify the value of any cell (including B1, B3, or B4), the STOCKHISTORY function will recalculate based on the new values of the cells.
Moreover, utilizing Stock data allows you to identify the currency in use. Enter the formula '=B1.Currency', where B1 refers to the cell containing the Stock data type.
Example 3
The following example illustrates all the attributes of the STOCKHISTORY function:
STOCKHISTORY('MSFT','1/1/2019','12/31/2019',2,2,0,5,2,3,4,1)
The function utilizes all inputs to generate a 2D data array. This data pertains to Microsoft stocks from January to December 2019, including trading volume, open, high, low, and traded stocks in each month.
In the function:
- 'MSFT' represents the stock code for Microsoft.
- '1/1/2019' is the start date.
- '12/31/2019' is the end date.
- Value 2: provides an overview on a monthly basis.
- Value 2: includes both stock code and item name in the returned result array.
- Values 0,5,2,3,4,1: represent the properties you want to display in the result array. In this case, it includes date, trading volume, open, high, low, and closing stocks.
- 'MSFT' is the stock code for Microsoft.
- '1/1/2019' is the start date.
- '12/31/2019' is the end date.
- Value 2: provides a monthly overview.
To select this data, access the Insert tab, then find and click Recommended Charts =>All Charts =>Stock and insert a chart for trading volume - open - high - close - closing stocks.
In this article, Mytour has introduced you to the STOCKHISTORY function in Excel. Additionally, readers can explore other articles on Mytour to learn more about vlookup function, examples, and how to use it.
