Forum Discussion

amit1601's avatar
amit1601
Brass Contributor
Jul 11, 2023
Solved

Stockhistory function is suddenly blocked

I have been a subscriber of office 365 and regular user of stockhistory function in excel for past 3 years. Today , all of sudden, my excel shows me error that I need to  sign in with an account asso...
Formulas and Functions
  • amit1601's avatar
    amit1601
    Jul 11, 2023
    No update on how and when it will be resolved. But I've a work around that you can do - save the excel in one drive and then using the one drive URL of excel open it on a browser. It will work fine.
MikuSippy's avatar
MikuSippy
Brass Contributor
Jul 11, 2023
I pray this is an isolated case for a few people. I just need the data as at 10 Jul 2023 to prepare the calculations. I also hope the whole thing can be fixed by this week. I am in Asia, and it is also 12 Jul. Of course the data is not yet updated for 12 Jul at MS servers (as I usually understand). The latest data for 11 Jul should by 12pm here. (Usually 24 hours behind as I have observed).

Keeping fingers crossed.
TheSimArchitect's avatar
TheSimArchitect
Brass Contributor
Jul 11, 2023

MikuSippy 

 

Are you able to use yours?

 

It seems that now StockHistory works!

 

But I have it wrapped with SumProduct and that function is causing the error now, when it worked just fine before. 

 

Oh yes, I used SumProduct because Excel returned it as an array, not as a number, therefore SumProduct was necessary to make the quote usable.

 

They didn't change the description of the command on their knowledge base. Is excel behavior going to change and we're supposed to edit our spreadsheets to account for it?

I will need to check why I was using SumProduct to wrap it, I likely fixed another issue following advice from someone on line a very long time ago. 😬

 

But that's good, nonetheless.

  • TheSimArchitect's avatar
    TheSimArchitect
    Brass Contributor
    Jul 12, 2023
    Try Ctrl + Alt + F9 on your keyboard with your spreadsheet open. That should force refresh it.
  • marketbusiness's avatar
    marketbusiness
    Copper Contributor
    Jul 12, 2023
    mine is not working , there is no excuse for it not to work...it's basic retreiving of end of data quotes.
  • MikuSippy's avatar
    MikuSippy
    Brass Contributor
    Jul 11, 2023
    Ah that is a good one, Recalculation! Many thanks, just really relieved things are back to normal. But of course, we know this function will have recurring problems, as noticed in the past.
  • TheSimArchitect's avatar
    TheSimArchitect
    Brass Contributor
    Jul 11, 2023
    Found a solution!

    Open the spreadsheet broken.

    Use, on your keyboard, Ctrl + Alt + F9

    That forces excel to recalculate everything and fixes it. :fireworks::fireworks::fireworks:
  • TheSimArchitect's avatar
    TheSimArchitect
    Brass Contributor
    Jul 11, 2023

    TheSimArchitect 

     

    It's not the SumProduct Formula being broken

     

    Weirdly enough, if I open a cell, edit it (even to insert an error), then edit it back, it works.

  • TheSimArchitect's avatar
    TheSimArchitect
    Brass Contributor
    Jul 11, 2023

    MikuSippy 

     

    Yes, I can use StockHistory but instead of returning an array it's returning a value. That means I am unable to wrap it with SumProduct.

     

    I will batch edit my entire spreadsheet to be able to work, and if necessary I'll change it back later. 🤪

  • MikuSippy's avatar
    MikuSippy
    Brass Contributor
    Jul 11, 2023
    Are you able to use yours? <<< Yes, I am able to use the function, back to normal life 🙂

    I have not used the sumproduct function, so I am unable to say much.

    Thank you.

Resources