Forum Discussion
amit1601
Jul 11, 2023Brass Contributor
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 associated with a subscription to user these data types. Select "sign in " at the top of excel. I am very well signed in , and can see that my office 365 family subscription is still alive. What am I missing here?
- 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.
- cnr002Brass ContributorDoes Microsoft ever formally respond to issues or send out a message that they have resolved the problem? I don't think that is too much to ask for!
- MikuSippyBrass ContributorI doubt they do, though they are aware of the issues and fix it. Might not even explain the cause(s). though the function is now working.
- marketbusinessCopper Contributor
there was a security update for office 365 on july 11 and it appears the stock data stockhistory feature breaks the application everytime there is an security update..which is why they have a security update.
- MikuSippyBrass Contributor
Gooood morning.
Wondering whether you have faced another issue with this function? Doesn't seem to work for 31 Jul 2023. Not for the currency. It looks ok for shares. - dcox9999Copper Contributor
Stockhistory downloads is now working, perhaps either upgrade KB5028185 and/or KB5028851 on July 12 fixed this issue. Thanks Microsoft...marketbusiness
- MikuSippyBrass ContributorYes, all is well now. Until the next time. Hope it does not recur?
- marketbusinessCopper Contributor
it works after going to account and switching licensing and re-entering email and accounts. which indicates their is a problem with licensing authentication, security issues, there, server is receiving too many request. as stockhistory can receive dates up to 365 days. The server needs to put limits in how many quotes or symbols you can request like max 200 symbols etc or how many stockhistory. dates Each quote request puts load on the computer. Some financial websites even charge per quote. if you have tonnes of quotes that puts too much load on the server. or a denial of service attack. the problem seems to go away after I go to account. in excel. switch license to 365 office instead of the home and student version and getting any updates which is probably security updates. Either some people who are not 365 subscribers maybe sending too many request even though they are not subscribers and resulting in denial of service on the server. if people have like 1000 symbol quotes on their spreadsheet it too many for updating and crash the server, I doubt anyone can monitor 1000 symbols in their portfolio.
- TheSimArchitectBrass ContributorYou're saying it works now, but we already said it works now. The system was not working before. No matter what we did, even if using Excel on line. It looked like a licensing issue as you mention, but it wasn't the case.
You may have multiple recurrencies for the same symbol on a large spreadsheet. I use it to convert currency across the time. I don't know how many occurences I have but if you consider 4 or 5 currency conversions per month and about 5 to 7 years, that makes about 300 ~ 400 recurrencies, even though only a few tickers are used, each quote for each time is a hit.
I had not only done what you said but I went steps further including the entire removal of Office 365 from my computer and a full reinstall. I also tried moving back and forth insider. And MS support were also aware of all steps involved as well, which didn't fix it.
But, yes, what you say is what occured to me because the error was related to licensing. This functionality broke before and was restored. I guess they might have changed something or they might have lost access to a data stream at some point.
Let's hope everything stays consistent from now on. Interesting to not have an official note from Microsoft on this. It would be useful.
Also, perhaps our files could store the information that's already downloaded and only change it if there's something valid from the server, otherwise keeping the existing calculations intact while just showing an error message to let us know the information being used might be innacurate. That way we would be able to continue to work even if that meant having a larger file in our systems.- marketbusinessCopper Contributor
I've posted another post on this issue and any future issues with stock data and stockhistory not working.
1. security updates for 365
2. check ALL connnected services on privacy settings in excel on
there is no need to send microsoft any diagnostic data and but they do need online content access for authentication on the experiences that analyze your content under connected 'experience' the wording is confusing. and people uncheck thinking they are sending personal information to microsoft.
you have to check all the CONNECTED EXPERIENCE section on the privacy setting to get online data like stocks, geography, currencies. those are ONLINE data and dynamic
FILE
OPTIONs
GENERAL
---scroll to privacy settings.. that is where most people get issues for no stock data. you need to give excel authorization to have access to online content or it won't work.
Reset licensing of 365 office again if you had home an office standalone. don't select that.
there is no way to uninstall home an office versions if you had them before on windows 10.
home office and student standalone don't have online connected services like stock data and stockhistory. you need a 'subscription' to receive those quotes.
The problems occured right after July 11 security updates for 365 and probably too many hacks trying to access the the data provider servers and not subscribers. or something.
As for currencies, the selection gives ETF etc. but who cares, anyone who does currencies can get that data for free anywhere for free from banks free of charge unlike stock exchanges who charge subcribers for stock quote and options quotes.
Again, there is no need to reinstall office 365 to get it to work, you'll just waste 1-3 hour of your time doing that.
- hariraongCopper ContributorThe issue is fixed for me since today morning. I did nothing. Stocks updating normally.
- Grant_TaylorCopper Contributor
hariraong Mine is fixed too since today. I also did nothing.
- marketbusinessCopper Contributor
it has to do with the Microsoft partner who is providing the data. they don't want it to work. and not for professional use or you be paying a lot more for data. it's suppose to be for fun right? not for 'professional use' so who cares if you are not getting quotes right? if you are using the stock data quotes for professional use and it doesn't work for a week, you are losing money and the stress could affect your work. A professional would not use these data provider that microsoft is paying peanuts for. Either microsoft is unwilling to pay for the data quotes or the problems are deliberate, and don't want it to work. there is a disclosure saying the quotes are not for professional use so who cares if the stock quotes flaky software. if your only reason for subscribing to excel is for the stock quotes etc. getting another quote provider. they pay charge like $40/month using the web interface or javascript to pull quotes into browser. instead of using excel. excel is just like a web browser if it can retrieve data from the internet.
- TheSimArchitectBrass ContributorI don't know if that's the case, even though you make a good point.
Current Currency Exchange Rates were working normally. Only StockHistory was broken. Historical data is likely scraped by Microsoft and kept on their servers. It would make no sense for them to keep "annoying" a third party service with repeated requests instead of using their own servers.
It's weird their software presented a licensing error message instead of something else related to the data obtained.- MikuSippyBrass ContributorMight be a matter of time that the function ceases to work. All the hard work and prior files saved for each month become useless. Time to backup the data with the actual historical data manually.
Unless there are lower cost paid 3rd-party services that replaces the stockhistory().
Anyone know about this?
- clearenergyCopper Contributor
Have found a fix which worked for me: Found it using Bing chat:
"If the STOCKHISTORY function is not working:
If you are using Office 365, try performing an Online Repair by right-clicking the Windows Start button, selecting “Apps and Features” on the pop-up menu, finding and clicking your Microsoft Office 365 application in the list, selecting Modify, then “Online Repair” and following the onscreen instructions."This got rid of the error message and blocked STOCKHISTORY cells. Still one or two cells blank, but mostly working. Must be some major issues somewhere.
Hope this helps.
- Tony_AdamsCopper Contributor
I tried something that seemed to work. Open the workbook that has the update. Save is as the same name with a-1 suffix. Close both worksheets. Open the original worksheet under old name.
it worked for me but I don't know if this is a permanent fix or just a one time fix.
- TheSimArchitectBrass ContributorThey are working on it, I think because I don't get the licensing error anymore, upon opening the spreadsheet.
Your method did not work here, though. Sadly.
- TheSimArchitectBrass ContributorI went even further and uninstalled office entirely, then installed it again, following MS Support.
It did not work.
Plus the same issue happens using the web browser version of Excel. They should be independent of each other, shouldn't they?
- TheSimArchitectBrass ContributorWow! That's nice. They didn't tell me anything yet and I opened my ticket yesterday. I was terrified of losing all my OneDrive synchronization (I use multiple accounts because of the 1 TB limit and I have tons of junctions and links LOL).
- ScubskiCopper ContributorI contacted MS support yesterday on this issue - they investigated, but couldnt fix in Tier 1 support They told me they would escalate to Tier 2 support - I'm now awaiting their response.
- ScubskiCopper Contributor
I've just had a call back from MS Support: They are aware of the issue and have a team working on it - no estimate of when it will get fixed but as reported by another post on this forum, there was a similar issue in Feb which took ~4 days for MS to fix (so don't hold your breath for a speedy resolution to this!)
- TheSimArchitectBrass ContributorI can personally survive if it takes 4 days.
Otherwise it will be hell to replace the function with something else.
I hope it's something broken they'll fix and that they're not deprecating it without giving us enough time to at least convert the results from the formulas into values.
Otherwise I'll have tons of currency rate values to update manually. 🤪
- JerryDNYCCopper ContributorI've been working with STOCKHISTORY and the Stock Data Type since they were introduced. I was in the middle of putting together a Stock Portfolio yesterday putting together a complex start and end dates for STOCKHISTORY when I abruptly got the #BLOCKED! message. I was logged in, and checked my account. Everything is in order, but I found it interesting that my 365 subscription just renewed on the first of this month. What I wonder is how is this not fixed quickly. It's been well over 12 hours and no fix? It makes me suspect of their security!
- Et1212Copper ContributorSame here. In addition to stock history (showing "blocked"), the stock data are not updating either!! Tell me to log into an account associated with a subscription.