CODEWITHSUNDEEP
excelnaminhistorystock
Quackr
Quackr

Reputation: 3

Eliminating #N/A errors in =MIN STOCKHISTORY results

I am using the STOCKHISTORY function to find the minimum value during a set period. On occasion and for some stocks, there may be errors or missing data on a given day. This returns #N/A value, I would very much like to eradicate these errors and/or count the errors as zero. Period of time covered in this instance is previous 365 days, C17 is referencing the 'stock' ticker.

Current formula is shown below:

=MIN(STOCKHISTORY(C17,TODAY()-365,TODAY(),0))

This returns #N/A due to missing data on some days in the data pull, an assumption based on previous experience with using STOCKHISTORY to actually pull daily data going back between 1 and 10 years. Ideally I would like to keep this contained within one cell, rather than pulling the data and then removing the #N/A errors which I have done in the past.

Appreciate any advice, although I have been working my way around Excel formulas recently, this one has me stumped.

Upvotes: 0

Views: 179

Answers (2)

J. Woolley
J. Woolley

Reputation: 88

The STOCKHISTORY function is currently not working correctly. Hope they fix it soon. I discovered if I open the workbook and wait 5 or 10 minutes, eventually formulas with STOCKHISTORY(...) will randomly update. It takes a lot of patience.

Upvotes: 0

rachel
rachel

Reputation: 1994

MIN ignores empty values, so you just need to use IFERROR to replace NA with "".

e.g below works:

=MIN(IFERROR(STOCKHISTORY("rddt",TODAY()-365,TODAY(),0),""))

Upvotes: 1

Related Questions