Stockhistory issue - inability to parse common some US Tickers

Question

In current updated version of Excel 365 running on OSX, =STOCKHISTORY("XNYS:AEM", WORKDAY(TODAY(), -150), TODAY(), 0, 0) and numerous other US tickers work perfectly and spill out the correct closing price history for the equities (in this case Agnico Eagle Mines Ltd).

However, the AU in the same formula =STOCKHISTORY("XNYS:AU", WORKDAY(TODAY(), -150), TODAY(), 0, 0) results in #VALUE!. I assume the 3rd party that Microsoft uses has incorrect data or identifiers but am uncertain.

Using this same STOCKHISTORY formula with "OTCM:AUSGF", "OTCM:HRZMF" and many other mining companies all fails.

Answer 1

Hi, James R Thompson

This looks less like a formula problem and more like an instrument-matching problem inside Excel’s stock data service. Both XNYS and OTCM are supported exchanges, and Excel can struggle to match some symbols cleanly unless you use the Stocks linked record/selector first.

Here are some suggestions you can try:

Instead of typing "XNYS:AU" or the OTC symbols directly into STOCKHISTORY, enter the ticker or full company name in a cell, go to Data > Stocks, and use the selector pane if Excel prompts for a match. Then point STOCKHISTORY to that linked stock cell rather than the text string, since it is recommended using a Stocks data type cell as the input.

You can also open the linked stock card first and confirm fields like Exchange/Previous Close. If the linked record does not resolve correctly there, STOCKHISTORY usually will not resolve it either. A suggestion on similar cases is to rely on the data selector to get the exact instrument Excel recognizes, rather than assuming the typed market code is the one its feed wants.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

I find it difficult to believe that LSEG Data & Analytics has zero stock history for Agnico Eagle Mines Ltd, one of the largest gold mining companies in the world. This answer for the over-the-counter tickers that are failing is more believable, though very problematic.