We use the BDH function to get closing prices at the end of each trading day, and do this for a list of different types of securities and indices at once.
Currently every BDH-of-index in the list refers to the same date at the top of the sheet, and if one index gives N/A
because there is no trading on that day, we manually make it refer to a cell with another date.
=BDH($B4&" index","px_last",$I$1,$I$1)
Where B4
refers to an index ("SPX" etc.) and I1
= yesterday's date.
I've just written a VBA routine that updates the dates at the top, but now I want it to also check if any of the indices gives an N/A
and, if so, let that one refer to another cell date automatically.
Can someone give me some advice on how to check the values in a row ranging c4:c20, and then change the cell that formula uses. Or should I alter the formula itself as well?
You could probably use an override instead:
=BDH($B4&" index","px_last",$I$1,$I$1,"Days=A,Fill=P")
That will retrieve the last available price as of yesterday's close, which may be the day before yesterday's close (or an earlier date) if that specific instrument did not trade yesterday.