Forum Discussion

MatthewM1900's avatar
MatthewM1900
Copper Contributor
Jul 19, 2024

Stockhistory data doesnt update for today but using the STOCK.Price function does

Using stockhistory formula on some stocks to pull stock price for 3-months or 1-year historical. However, they never include todays stock price even a few hours after close. I checked (https://support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6)) which states that the delay is 15 mins for US stocks yet it wont show these values in stockhistory formula. It does work if you use STOCKCELL.price but wondering why there is a discrepancy here.

excel
  • MatthewM1900's avatar
    MatthewM1900
    Copper Contributor
    Jul 26, 2024

    Any official response to this? The Stock.Price function works and updates throughout the day yet we cant pull stockhistory till a day later. 

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 26, 2024

      MatthewM1900 

      That's in documentation:

      • STOCKHISTORY, in showing historical data, generally only updates after a trading day completes. This means that you cannot use STOCKHISTORY to get data for today's trading details until after the market has closed or after the day has completed depending on the market.

      STOCKHISTORY function - Microsoft Support

       

      • MatthewM1900's avatar
        MatthewM1900
        Copper Contributor
        Jul 26, 2024

        SergeiBaklan 

         

        Ok this makes sense but I do not think it works this way. I am currently updating a dashboard and the US stocks still are not update for todays closed even though its been ~1.5 hours since close, yet Price.Stock works so the data is there it just does not seem to flow into Stockhistory. This clearly does not align with what you highlighted how "only updates after a trading day completes" as a trading day has completed. 

         

        I think the documentation should read as follows: (I will need to check on my end at what time I see the stockhistory begin to show the next day, my guess is its after 12am EST but will need to verify)

        • STOCKHISTORY, in showing historical data, generally only updates ONE DAY after a trading day completes. This means that you cannot use STOCKHISTORY to get data for today's trading details until THE DAY AFTER the market has closed for US markets, times may change depending on the market

        From a user perspective the data is clearly in the system (Stock.Price is updated)  but for some reason Stockhistory can't display it. Would require someone much smarter than me to figure out whats going on in the backend and why its not showing.

         

        Thanks,

        Matt

         

Resources