Forum Discussion
[52 week high] formula not working
I have a spreadsheet where I track my portfolio data. I have attached a sample. It appears that the "[52 week high]" formula is not working properly. It shows up as the same price as the current "Price" for most securities. While this is in theory possible, the "Price" is below the "[Previous close]", so therefore it appears incorrect. Is this a known issue? How can it be resolved?
7 Replies
I've looked at your file. The issue is a mix of two things:
- Your live data link is broken – Column D shows #VALUE!, meaning your price data is just a stale snapshot. That's why the price seems inconsistent with the previous close.
- For ETFs, the 52 week high field is often unreliable – Refinitiv frequently doesn't supply it, so Excel fills it in with the current price as a placeholder—that's why they match.
Here is what to do (in this order):
- Refresh first: Go to the Data tab and click Refresh All. If the #VALUE! disappears, you're good.
- If not, re-link the tickers manually: Delete the ticker in the cell (e.g., CNX1), type it again, and wait for the small building/stock icon to appear before pressing Enter. Do this one cell at a time for each ETF.
- Check your account: If the refresh doesn't stick, go to File > Account and confirm your Microsoft 365 subscription is active and signed in—the Stocks feature relies on that login.
- For a reliable 52-week high, try this formula in a new column:
=MAX(STOCKHISTORY(D8, TODAY()-365, TODAY(), 0, 0, 3))
(The 3 correctly requests the High price.) - Test each ticker separately – it might work for one ETF and not the other. If it returns an error, you'll need to get the 52-week high manually from the iShares factsheet or JustETF as a static fallback.
*The example file has been inserted.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Did you ever figure this out? When I use the formula it returns "#FIELD!"
