Numbers.app: IFERROR with STOCKH (getting around the 'Weekend' Problem...

[spetznatz]

Hi,

I'm building a spreadsheet Using STOCKH to track my investments [STOCKH("SYMBOL", close, DATEXPR)], showing current prices, yesterday's close, a week ago's close back to 5 years ago's close. I'm using EDATE to calculate the date offsets from the current (today's) date. The problem is, if any of those dates falls on a weekend or a holiday, STOCKH returns an error (why STOCKH doesn't just copy the previous day's close into holidays and weekends is beyond me).

So, I want to use IFERROR to decrement the date value by 2 days if it's a holiday or weekend (I know it should be more complex than that), but I just can't figure out the syntax: I tried IFERROR(STOCKH("SYMBOL', close, DATEXPR), DATEXPR -"2d"), but no luck... Googling hasn't been helpful, either. Could somebody point me in the right direction?

Also, If anyone knows how to embed the current date into an expression, I'd be really grateful.

TIA

 

[James_C]

Hi @spetznatz I have got a solution for you. I think the easiest way is to check if the date that the formula is using falls on the weekend using the WEEKDAY formula. Then if the value is 6 or 7 use an If statement to count back to the last Friday. Do that for Todays date, Yesturday's date and 5 years ago then build your STOCKH formula.

See below

You can download the Numbers file I created here.