Announcing STOCKHISTORY
Published Jun 10 2020 10:17 AM 142K Views
Microsoft

Disclaimer
STOCKHISTORY requires a Microsoft 365 Personal, Microsoft 365 Family, Microsoft 365 Business Standard, or Microsoft 365 Business Premium subscription. 

 

Excel is introducing a dynamically updating service-backed function that builds on the flexibility of dynamic arrays and fills a gap in the Stocks data type by providing access to historical data. 

 

We have added many needed improvements to support service-side errors and data retrieval, including new errors like #BUSY! that show in Excel while data is being processed. We also added a way for the function to spill formats dynamically instead of stamping a format on a cell.

 

Introducing STOCKHISTORY

 

StockHistoryChart1.png

 

Easily pulling stock prices into Excel has been one of the most requested features on UserVoice. The Stocks data type began this journey by enabling refreshable quotes for stocks, bonds, funds, and currency pairs. We know that this wasn’t enough to complete the goals you have for analyzing your portfolio in Excel. Getting the history of a financial instrument over time is crucial information you need. We are thrilled to be able to bring this functionality into Excel.

 

Getting started is simple

 

StockHistoryFunction1.png

 

Let’s look at an overview of the function signature:

 

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

 

  • stock: The identifier for the financial instrument targeted. This can be a ticker symbol or a Stocks data type.
  • start_date: The earliest date for which you want information.
  • end_date (optional): The latest date for which you want information.
  • interval (optional): Daily (0), Weekly (1), or Monthly (2) interval options for data
  • headers (optional): Specifies if additional header rows are returned with the array.
  • property0 – property5 (optional): Specifies which information should be included in the result, Date (0), Close (1), Open (2), High (3), Low (4), Volume (5).

You can find more detailed explanations of these arguments on the STOCKHISTORY support page.

 

Examples

 

Using Basic Text Inputs

 

=STOCKHISTORY("MSFT", "6/1/2020", "6/5/2020")

You can get started by using the ticker symbol as a textual representation of the stock. The start_date and end_date input will be interpreted by Excel the same way the DATEVALUE function would interpret date_text today. This means that the date inputs respect your system settings for datetime.

StockHistoryTextExample.png

 

Debugging Tip: Make sure you have any text values surrounded with quotation marks in your function inputs.

 

Choosing a Specific Exchange

If the ticker symbol is used as input without an exchange specified, the values will normally default to the XNAS (NASDAQ) stock exchange. There are two ways to help Excel know which financial instrument you are targeting. 

 

Using prepended text

First, you can use the Market Identifier Code, followed by a colon, followed by the ticker symbol to get data from a specific exchange.

 

=STOCKHISTORY(“XMIL:MSFT”, TODAY()-7, TODAY())

In this example, I am specifying the MSFT symbol on the Borsa Italiana Exchange ("XMIL:MSFT") and I am requesting the last 7 days of data using the TODAY() function. This function updates when the date changes and so as a part of calculation the dates shown in the grid will update as well. Notice how the dates shown skip non-trading days like weekends, and the prices shown are in euros.

 

StockHistoryExchangeExample.png

 

Using a Stocks data type

Second, you can use a cell containing a Stocks data type as a reference in the function, just like any other cell references.

 

=STOCKHISTORY(B1, B3, B4)

Below I have converted MSFT to a Stocks data type and chosen the data type for Microsoft from the Mexican Stock Exchange. I passed the cell containing the data type to the STOCKHISTORY function (B1). I also passed in references to cells containing dates (B3, and B4). If I edit any of these cells (B1, B3, or B4) the STOCKHISTORY function will recalculate based on the new values in the cells.

StockHistoryReferenceExample.png

 

Additional benefits to using a Stocks data type includes allowing you to see which currency the prices represent. Try the formula ‘=B1.Currency’ where B1 references a cell containing a Stocks data type.

 

CurrencyExample.png

 

Debugging Tip: To verify what exchange your stock history values are coming from, input the [header] argument of “2” (Show instrument identifier and header) into the formula.

HeaderArgumentExample.png

 

Getting Fancy

Let’s see an example using every argument and property in the function.

 

STOCKHISTORY("MSFT","1/1/2019","12/31/2019",2,2,0,5,2,3,4,1)

This function uses every input field to create a wonderful 2D array of data for me to analyze. This data represents Microsoft stock from January to December 2019 in a monthly overview for the Volume, Open, High, Low, and Close for that month. My goal is to chart this data in Excel.

 

Let me give a refresher on what this signature represents:

“MSFT” – The Microsoft ticker symbol,

“1/1/2019” – The start date,

“12/31/2019” – The end date,

2 – Indicates a choice to show the Monthly overview,

StockHistoryLongExample-Interval.png

 

2 – Indicates a choice to include both the ticker symbol and the label row in the resulting array,

StockHistoryLongExample-Header.png

 

0,5,2,3,4,1 – Indicates the properties I want, in the order I want them in the resulting array. In this case, Date, Volume, Open, High, Low, Close.

StockHistoryLongExample-Properties.png

 

All together this is the resulting formula in Excel with the data spilled into the grid.

StockHistoryLongExample.png

 

Then I can select this data, go to the Insert tab, click Recommended Charts >All Charts>Stock and insert a Volume-Open-High-Low-Close chart.

StockHistoryInsertChart.png

 StockHistoryChart2.png

I can use this function to produce many charts and graphs, as well as use the data as inputs into other Excel functions. Try out different uses and combinations and let me know what you think!

 

About our Data Sources

As with Excel’s Stocks data type we are sourcing the historical data from Refinitiv. You can read more about what exchanges are currently supported in Excel. While Stocks data type values can mostly be refreshed intraday on minor delay, STOCKHISTORY values are updated once daily for all supported exchanges at a minor delay after market close.

 

The STOCKHISTORY function also supports inputs of Currency Pairs, ETFs, Index Funds, Mutual Funds, and Bonds. The easiest way to specify these inputs is to convert them to the Stocks data type and use them as references to the function.

 

Please note that while some financial instruments may be available as Stocks data types, the historical information will not yet be available. For example, this is the case for most popular Index Funds including the S&P 500.

 

Numbers with Format Hints

STOCKHISTORY is the first function that provides formatting hints for its number. It does so by returning an enhanced number type that we refer to as a Formatted Number Value (“FNV”). FNVs are just like any other number in Excel but they are accompanied by a helpful formatting hint. Excel uses this format hint when the value lands in a cell with “General” number formatting applied, the default for unformatted cells.

 

In the example below, the STOCKHISTORY function is being used to retrieve the exchange rate for a currency pair between Jan 1st and Jan 7th, 2020. All the cells have the default “General” number format applied. Notice how the FNV’s (indicated with colored overlays) allow the Close column to seamlessly change from Pound (£) to Dollar ($) to Rand (R). In the example, FNVs are also used to have date serials (e.g. 43831) show using date formatting (e.g. 1/1/2020).

 

=STOCKHISTORY(C2, "1/1/2020", "1/7/2020")

FormattedNumberValueGIF.gif

 

 FNVs can pass through lookup style functions such as XLOOKUP, INDEX, IF, CHOOSE, or simple references (e.g. =A1) but the hint is simply ignored by other function which just see them as their number value.

 

Learn More

You can learn more about STOCKHISTORY by reviewing the STOCKHISTORY support article. This article goes in depth on the function definition and clarifies how monthly and weekly intervals are determined.

 

Availability Notes

STOCKHISTORY is now available to 100% of Microsoft 365 Subscribers, on Mac, Windows, and Excel Online.

 

Please continue to provide feedback either in the comments below or through our in-app feedback. Please let us know what you think!


To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also follow Excel on Facebook and Twitter

 

Kaycee Reineke (@KayceeSue)
Program Manager, Excel

102 Comments

@KayceeSue , thank you! That's really great function itself and introduction of FNV is even more great.

Some difference with Data Type

image.png

Microsoft

@Sergei Baklan great observation. I can add some clarity on how this currently works. The historical data is added at the end of the trading day when the market closes. Typically within a couple hours of that close. The Stock data type shows 'real-time' data according to the delay agreement we have with the data provider. Currently intraday history isn't supported in our licensing of the data for Excel. What impact does intraday history play in your analysis? 

Brass Contributor

@KayceeSue Are the Formatted Number Values ("FNV") available to third-party developers writing custom Excel functions? We have started leveraging the dynamic arrays feature in Excel with our custom functions and are exposing tabular data similar to how you implemented STOCKHISTORY.

 

The top requirements I have at this point are to provide formatting for the column headers (bold!) and to do something with date/currency values we return. Where can I find technical information on this feature?

 

 

I can confirm that STOCKHISTORY has been flighted to my system

 

 

Kudos to you all

 

 

Cheers

Abiola David

 

@KayceeSue , it's more clear now, thank you for the clarification. So far I don't how critical this day, depends on how to use this function in application. Dates without sales are also missed, in some cases it's much better to have all dates. Will see, need to play more.

Copper Contributor

This is a wonderful update, thanks team! 

Can't wait to get a chance to try it :)

Thanks

Jeff

Microsoft

@GabrM Thanks for the feedback! FNV's are not yet available to 3rd party UDF providers, but this is something we are keeping an eye on. To ensure we have the right design, we wanted to first pilot FNV's with a 1st party function (STOCKHISTORY) before broadening out the feature based on user demand. 

Brass Contributor

@JoeMcDaid thanks for your reply. This is a great improvement and I'm happy to see that dynamic arrays being leveraged by Microsoft in this way.

Great work Kaycee and Team 

Copper Contributor

RasnaSaini_0-1591858665812.png

That's my version and I just updated my office. I thought I should be able to use the stockhistory function, but it doesn't show up. What can I do? 
This function is going to be a game changer in my industry and I cannot wait to use it! (You should be able to smell my excitement!!!) 

@RasnaSaini you're not in the lucky 50% sorry  (if it's any consolation neither am I).   We have to wait a little longer

 

 

image.png

Hi @KayceeSue,

that's awesome - thank you and thanks to the team : -)

I have seen, that the German translation for this function is BÖRSENHISTORIE - I love it : -)

Best,

Mourad 

Copper Contributor

Thanks @Wyn Hopkins - I did read that 50% thing, but didn't want to acknowledge that I lost in the excel lottery. I am hoping that there is someway to get on to the other side - I really can't wait to use it! 

Microsoft

@RasnaSaini , I understand your disappointment at not yet having access to the feature even though you are in the beta channel. Be assured we are trying to get to the next release as soon as possible and will update this thread once that happens. In the meantime is there anything in this blog or from other online posts that stands out as something you expect that isn't covered, or some behavior described that doesn't make sense? I know you will have to try it to see how it feels, but if there are any red flags, that is what we are trying to address right away.

🧪 🧫 Experimenting with the new #STOCKHISTORY function & I found very interesting.

So I have an idea :light_bulb: hope you will support :handshake: by voting 🗳 and make it happen.

STOCKHISTORY function currently accepts only the numeric values in the properties it would be great to see if it accepts the string values and make it more flexible for the users.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/40652983...

0.jpeg

 

Regards, Faraz Shaikh

Copper Contributor

@KayceeSue Thank you so much for your response. One of the things that I wanted to check, but couldn't find further information to, is how are stock splits (reverse splits) handled by this function? For eg: Let's assume Amazon stock is currently at $2,500 and they decide to do 5 for 1 stock split. The share price would fall to $500 (and would perhaps settle a little over it reflecting wider market participation at lower price hence pushing the share price up). What would the output look like using #Stockhistory? Would it index the price and volume prior to the split? 

 

Next, ex-dividend date - any possibility of showing that through the function? This is when share price typically falls to reflect the dividend cash outflow. 

 

My third suggestion is the same as @Faraz Shaikh - to have string values for properties to make the function more user friendly during audits. 

Hi,

the idea for having the arguments (also) available as text strings is attractive. In a such case, I think, that they should then also be localized, because it makes not too much sense for example for a German, French or Spanish user to use English named text arguments.
However, assuming that these text arguments would not be automatically translated by Excel from one language to another, this could lead to problems when sharing the workbook with collegues in other countries. In current (and past) versions of Excel, CELL is an example of a function showing this behaviour.
Therefore, I would personally prefer to keep the arguments as values for avoiding compatibility problems when using Excel in different languages.

Best,

Mourad

@Mourad Louha , fully support you. We have enough other issues working in international environment, not necessary to add more.

Microsoft

@RasnaSaini  Dividend dates and Ex-dividend dates are not something currently licensed to us by our data provider. It is one of the top asks from customers like you though, so I understand how it would make sense as a factor for historical analysis. I encourage you to vote for the item here: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/35122639... on UserVoice. I can't make any promises about when/if we can get the data, but it is on my radar. 

For Stock Splits, when the stock split data flows through to our system, there is a latency period, on the next recalculation of the StockHistory function (this could be on file open, function edit, or because the function is volatile with the Today() argument etc), the results will update to show the historical prices calculated to reflect the split. This will feel very similar to what you may see on websites like MSN, Google, and Bing when showing historical prices https://www.msn.com/en-us/money/stockdetails/nas-msft/fi-a1xzim
There wouldn't be a way to "freeze" the numbers from the result unless you performed a copy and paste as values for the data before the Split. Does that make sense? What do you think?

 

Microsoft

@Sergei Baklan @Mourad Louha @Faraz Shaikh 
This is some great discussion about how Excel can explore being more user-friendly for function arguments to aid in audits and clarity for laying out your data. For some context on our choices, Excel will typically NOT change arguments to your formulas when input as strings, so if you put in "1/2/20" as a string into this function in the UK that would be February 1st and in the US it would be January 2nd. But if you put "1/2/20" in a cell from the US and then referenced it in your formula, Excel doesn't 'see' the date formatting, it sees the number 43832 and so you and the person in the UK would see the same data. In the same vein, Excel typically uses numbers to represent defined properties as inputs to formulas (sometimes called ENUMs) to simplify these cross international concerns. 

You will notice that the "headers" (or other information that is returned as a result of formulas) will be 'translated' to show in the language your Excel is in. This is often what happens for the outputs of formulas or for things Excel controls. We like to give you control over the input, but we can sometimes be smart about the output. So this isn't to say that we couldn't include textual inputs for properties, we just haven't added the additional translation layer into the system, and would introduce complexity for sure. Thanks for suggestion and the UserVoice, if this gets traction, we could revisit it.

 

Sorry for the long-winded explanation. Hope some of that helps. 

Copper Contributor

@JoeMcDaid Definitely +1 for please adding FNVs to the C API too - so that .xll-based UDFs can use these too.

Iron Contributor

I am an insider but still do not have the function. When is it scheduled to be available for all insiders?

 

Thanks, Mike

Iron Contributor

Meanwhile arrived at the 132 12 20 000 build but still not available. Any possibility I can force this function?

Microsoft

@Mike There isn't a way to override into the 50% of Beta channel. The good news is while I can't give you a date, we are working hard to move to the next percent of customers hopefully within 2 months. At that time 100% of Beta channel will have access. I will update this post at that time. Fingers crossed for smooth sailing between now and then. 

Iron Contributor

Hi @KayceeSue ,

Thanks a lot for your answer.

Microsoft

Hey Everyone, we have move ahead with our next ring of release, and so anyone in the Beta Channel of Office Insiders should 100% have the StockHistory function available. If you do not, please let me know!

Iron Contributor

Hi @KayceeSue 

 

Got it, thanks a lot!

Copper Contributor

It's not working on mine yet, what can I do to get it to work? I have beta. @KayceeSue 

build 13304.20000 (if that matters)

Copper Contributor
Was awaiting this release to try out the function. Thanks for the wider release!
Here’s what I wrote on LinkedIn today about Stockhistory - explaining the function in simplified colloquial language and then building complexity over it.  
 
 
Copper Contributor

I have found that STOCKHISTORY has limits. For example it only goes back as far as 2017 for some European stocks. Only as far as 2014 for GOOG as another example. Is that the way it should be? Is that the permanent plan? Will more history be available in future? ...

Copper Contributor

Hi @KayceeSue 

I have Office 365 Excel beta version 2011 (Build 13415.20002 ) but don't have this function yet. Eager to chart historic data, what can i do to get it?

Microsoft

@Rutu_J Can you confirm that your channel is set to Beta Channel on your accounts page? It is possible you signed up for Office Insider but still need to switch the Insider Level. 

If that does say Beta, what is your language, it is possible the function is translated. 

 
 
 
 
 
 
 

Beta Channel.png

@KayceeSue , by the way, how is version 2007 on Beta channel?

Copper Contributor

@KayceeSue  Hi Kaycee  Yes it shows beta just like your image.

It's working now. I shut down my PC and then started it again. And it started working. 

 

@Sergei Baklan  It now shows version 2011 (Build 13415.20002) after shutting down and starting again..

 

I'm using STOCKHISTORY with Sparklines , it's such a useful combination!

Copper Contributor

I just want to say THANK YOU! For months, I would manually build my weekly day trade/swing trade stock scanner in excel, by analyzing the highs and lows of my stocks of interest from the last week and month. Sometimes this would take HOURS! But now with a combination of STOCKHISTORY, MIN, and MAX, I can set up my scanner in a matter minutes! Good job and thank you!

Copper Contributor

Hello @KayceeSue 

 

The Stock history for shares that have undergone stock splits does not seem to be adjusted for the same, rendering the chart very different from what can be seen on other sites like Google etc. Is this expected behaviour ? The stock i tried it for is this 

HCL TECHNOLOGIES LIMITED (XBOM:532281)

 

Thanks

Copper Contributor

Hi - I really appreciate that you're adding this feature to excel.  I currently use an add-in to perform the function and would really like to have a native function.  Unfortunately the function isn't useful for historical information unless it returns the price adjusted for dividends (and splits) - so sadly I have to keep using my add-in.  Others in this thread have essentially asked for the same thing.  I'd go further and say that the function in it's current state is only useful for relatively short-term analysis where splits and dividends aren't relevant.  If you don't realize this then you can be deceived by using this function - especially in the case of splits.

Copper Contributor

FEATURE REQUEST

1 - It would be great you could could add a parameter to reverse the order the the data is written please! (i.e. most recent dates first). Most trading systems are use data this way.

 

2. As someone else commented above, an Adjusted Close price would also be good.

 

Big respect to you Microsoft for pushing in this area!! Getting Historical stock data from Google is not longer possible and pulling from Yahoo finance using their Cookie and Crumb routines is way over complicated for what it should be (I've spend hundreds of hours over the years fighting with VBA to get historical data into Excel)!

Copper Contributor

While Stockhistory sounds like a great function, it is useless without a column for dividend and split adjusted price.  Every other professional historical source provides this.  PLEASE INCLUDE ADJUSTED PRICE!

Copper Contributor

Hi,

 

I purchased Office 365 today and was able to access the STOCKHISTORY function. However upon restarting my PC, I am no longer able to use it. I had already altered several files quite substantially using the STOCKHISTORY function, and those files are now not working as they should.

Is access to the new formula temporary or is there some way I can get access again?

 

Thanks,

 

Wade

Copper Contributor

@KayceeSueKudos on the Stockhistory function. I've been using a 3rd party add-in for +10 yrs so this is a welcome feature for me, and I've signed up for Office365 to test it out. Works great, but some markets like Japan (TSE) and Singapore (SGX) are unavailable. Any plans to roll out data for these markets? and will this likely happen? Thank you.

Copper Contributor

Is it possible to include dividend information in this function?

Microsoft

@All This feature is now available to all M365 Subscribers using Mac, Windows, and Excel for the Web. Please try it out and continue to give feedback. 

@KayceeSue , for Excel Online it's less informative. For example, typing such formula

image.png

Excel Online doesn't suggest me an options for the intervals and other parameters and, in general, I have no idea what to use. Okay, I may check on desktop versions or perhaps on help page, but that's not suitable.

Copper Contributor

Hi @KayceeSue 

 

Any possibility to also get this feature for treasury bills? Not exactly "stock history" - but a reliable source in Data for either current treasury bill rates or historical ones would be amazing.

 

All this stock data in conjunction with Treasury yields (risk free rates) would simplify workflow and reduce the need for external data sources that tend to change in format over time.

 

Thanks!

Copper Contributor

The announcement states that historical data is not available for stock indices such the S&P 500 and that, unfortunately, seems to be true.   I'd like to ask you reconsider the omission because of the importance of the S&P 500 (often with ticker symbol = .INX) for tracking portfolio performance. I can approximate the S&P 500 index value by taking the price of the SPY or similar exchange traded fund applying a scale factor, but the scale factor isn't necessarily constant.  Direct access to the historical data about the index itself would be very much appreciated.  

Copper Contributor

anyone using this function please know that you can't use it to compare performance of any instrument (stock or ETF) that has split or paid a dividend - it will yield incorrect results.  For example just look at 2020 performance of VYM - the stock history function will tell you that it achieved -2.35%.  In reality, if you take into account dividends paid, the ETF yielded 1.15%.  The more dividends, or splits that occurred the more "off" the stock history function will be - so a 3-year, 5-year assessment will be WAY off.  IMO, this function is worse than worthless until they correct this.

Copper Contributor

@pdxtony I cannot replicate the issue you mention. Apple and Tesla both had splits and the stock history uses the adjusted pricing.

 

Dividends are a different thing entirely - but =dividendhistory and =tbillhistory as functions would be incredibly useful alongside =stockhistory.

Microsoft

@pdxtony I am not familiar with the specific way of calculating you are describing. But I can confirm that Splits are accounted for in the history of the data over time. When the data flows into our system from Refinitiv, on the next recalculation of the data you will see #BUSY and then the Split adjusted history will be shown. 

 

If that is not what you are seeing, please let me know.

Co-Authors
Version history
Last update:
‎Apr 09 2021 12:28 PM
Updated by: