STOCKHISTORY usage

Dear KHQD

Sorry to bother you again. We have also received replies from users in other language sections who encountered the same problem as yours. Some users replied that they solved the problem by changing the uppercase and lowercase of the function. You can use this as a reference:

English thread:

• Eliminating #N/A errors in =MIN STOCKHISTORY results - Microsoft Community
• I started getting #CONNECT! errors yesterday evening for stock-history - Microsoft Community

Therefore, we recommend that you try changing the case of the function to see if it can be temporarily restored to normal. We also recommend that you submit feedback for this issue so that the relevant team can pay attention to this issue.

Best Regards,

Tracy - MSFT | Microsoft Community Support Specialist

Dear KHQD

Thank you for your reply.

It needs to be explained that the calculation of the STOCKHISTORY function depends on a specific external service provider, which is an online service. Usually the prompt "connect" is related to the temporary server of the data source, which makes it impossible to return results.

But please rest assured that this problem usually does not last too long.

We also recommend that you submit it on theExcel feedback platform so that the relevant provider can notice the problem as soon as possible. If it is convenient, please provide as many details as possible, including pictures, copy videos, etc., where there will be dedicated developers to pay attention to and comment on your feedback.

After sending feedback, you can share the feedback link in this forum, so that other users with similar problems in the forum, including me, can vote for your feedback. The more votes, the easier it is to get the attention of the development team. Thank you for your cooperation.

Best Regards,

Tracy - MSFT | Microsoft Community Support Specialist

Thanks Tracy. I have checked all the changes you have suggested, but the issue with stockhistory persists. The biggest issue is that the data retrieval is erratic. Sometimes, the stock info obtained with stockhistory is populated, and other time many are not populated. So the only option I have is to close and reopen the workbook and hope it gets populated.

The command: =STOCKHISTORY(E82#,F81#,,2,0,1)

will produce data such as:

The connect issue is random. This causes excel to keep waiting until I hit ESC.

Hence making just one stockhistory call does have some issues.

On the other hand if I have individual calls like: =TRANSPOSE(STOCKHISTORY(E82, $F$81, CurrentDate,2,0,1))

I get output like:

At least this approach does not block and require me to hit ESC. When I see this type of missing data, I will close and reopen the workbook and often the #connect! issue will go away. Very erratic.

Dear KHQD

Thanks for raising your concerns in Microsoft Community.

We understand that you want to inquire about the use of the STOCKHISTORY function. We simulated a simple call, such as the following figure:

Usually, the busy prompt is related to the network connection, Excel version or settings, and the requested exchange.

Because you need to select an exchange to use stock data, the data corresponding to different exchanges will fluctuate, so this situation may occur and cause it to fail to load. Usually, the data will appear after a while after the busy appears. If it is displayed as busy for a long time, you can try to change the exchange.

STOCKHISTORY function - Microsoft Support

You can also try the following steps:

Disable Excel's hardware graphics acceleration（You need to close all Office components first）:

1. Press Windows key + R, enter regedit to open the registry editor:
2. Find the following path:

     HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Common\Graphics 
   

(If there is no Graphics, please create this item manually)

3. Select Graphics, right-click "New DWORD (32-bit)", name it DisableHardwareAcceleration. Double-click to open this key value and set the value to 1.
4. Reopen the Office component to confirm the situation.

Disclaimer: Generally, modifying registry subkeys or work group is intended for advanced users, administrators, and IT Professionals. It can help fix some problems, however, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For further protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, clickHow to back up and restore the registry in Windows - Microsoft Supportto view the article.

Change network settings:

• Change your computer's network connection, which can be switched to mobile hotspot.
• Click Windows+R and type ncpa.cpl.
• Click on the name of the network you are now connected to, open the Network Status window, click Properties, in the Network Properties window, double-click on Internet Protocol version 4 (TCP/IPv4), change the Auto-DNS to "4.2.2.1" and "4.2.2.2", and then click "OK".
• Tap Windows+R and type inetcpl.cpl to open Internet Options.
• Click "Connections" in the upper menu bar, click "LAN Settings", tick "Auto-detect settings", and make sure the remaining two are unchecked, click "OK".
• Click "Advanced" on the right side of the upper menu bar, pull down the slider, tick "Restore Advanced Settings", click "Apply" on the bottom right corner, and then click "OK".

You can try the above steps and then check the operation of Excel.

In addition, regarding the Let and reduce functions you mentioned, this is also possible, but it may cause performance problems due to multiple calls to the STOCKHISTORY function in the formula. Because your STOCKHISTORY formula is a direct usage, if the amount of data is not large, we would recommend that you use it. But if the range of the table is large, it may affect performance.

STOCKHISTORY is indeed volatile, because it is a dynamic array function, it will be automatically updated according to the update of network data. It updates data based on changes in the stock market, usually instantly.

If you prefer to have data updated less frequently, you can set the data update of the STOCKHISTORY function to manual. Although Excel itself does not directly support full manual updates, you can use the following methods to reduce the frequency of updates:

Turn off automatic calculation: Go to the Formulas tab, select Calculation options, and set it to Manual. This way, Excel will not automatically recalculate all formulas every time a change is made.

I hope the above information is helpful to you. If you need further assistance, please feel free to reply.

Best Regards,

Tracy - MSFT | Microsoft Community Support Specialist