Stock data updating as file opens

[Rory_B]

Something has changed in Excel for me. When I open a file that has stock data in it, it is updating the stock data immediately. It used to be I would open the file and then update the data.

I need to figure out how to go back to that method because I use a macro to copy yesterday's data to a new column and then update the stock information. Now the information updates immediately and I cannot get the previous day's data copied.

[#2 · Accepted solution]

@Rory_B 

Just watched the video minutes ago:

https://www.youtube.com/watch?v=W0vU24qx8aQ&t=86s

[#3]

That helped. I guess they added that feature recently.
I wish they had set it to manual by default since that was basically the default before they added the feature.
It is also interesting that is only in the right click menu on the stock data and not in the data section of the ribbon.

BTW I am only in Win10

[#4]

Thanks -- I also found that video helpful as I had the same problem as the OP.

Until recently, refreshing stock quote data in Excel was under my control, but lately it changed to occurring automatically after the workbook is opened. What's worse is that, unlike manual data refreshes ("Refresh All" in the Data ribbon), I can't undo (CTRL-Z) the automatically refreshed data to restore the data from the earlier time.

Anyway for those who aren't able to watch the video (I previously worked somewhere that blocked YouTube), here's how to disable the automatic refresh for the stock data type:

1. Right-click the stock data type icon in any stock data type cell. (I'm referring to the icon that looks like the front of the NYSE stock exchange.)

2. In the context menu that appears, choose "Data Type", followed by "Refresh Settings" from the sub-menu that appears.

3. In the Data Types Refresh Settings panel that opens, click "Stocks" to expand.

4. Under "Refresh this data type", click "Manually". You can close the Data Types Refresh Settings panel.

5. Save your workbook.

Finally, I wondered if that change applied only to the one cell -- it doesn't. Rather, it applies to the entire stock data type for the workbook, so it doesn't matter which icon you right-click to access the Data Types Refresh Settings panel.

[#5]

@Rory_B 

This menu is from right click on icon since it depends on which data type you use. One settings for Stocks, another settings for Organizational and you have no options for, for example, Geography.

Other words, there are no common settings for all data types.

[#6]

How do you get your old data back though? As soon as I open the 30/9/21 data goes before I can change it to manual update only.

[#7]

I guess you could use STOCKHISTORY().

[#8]

Thanks but there is me login in to do a ten second job and Microsoft are now making me rebuild five years of quarterly valuations. Do they consult on changes like this? So frustrating.

[#9]

@Toddfrey What if you were to go offline / airplane mode before opening your workbook? Excel can't update the prices if there's no Internet access.

[#10]

Thanks that worked @cwrea