Search code examples
excelexcel-2010bloomberg

Getting returns for a list of ISIN numbers from bloomberg

I have a list of ISINs.

AT0000697750 ISIN
AT0000499900 ISIN
AT00000OESD0 ISIN
AT0000676903 ISIN
AT0000937503 ISIN
AT0000938204 ISIN
AT0000645403 ISIN
...

Now I get with the formula, =BDH(A1;"px last";"01/01/1990";"") the stock prices for the ISIN number, which is in cell A1. However, my problem is that I get two columns back, one with the date and one with the price. So I have to align the ISINs with an empty cell. But pulling the formula along each column, does only give me the first colum of stock prices. My current structure looks like that:

enter image description here

My problem is that I need to change the cell A1 by hand to C1 for example or I do not get any further data back.

How should I structure my worksheet, to get the stock prices automatically and the output does not overlap?

Solution

  • You can retrieve the first, first, third, third, fifth, fifth, etc columns from across row 1 with a little maths.

    =INDEX(1:1, 1, INT((COLUMNS($A:A)-1)/2)*2+1)

    This would replace A1 in your first column formula.

    =BDH(INDEX($1:$1; 1; INT((COLUMNS($A:A)-1)/2)*2+1); "px last"; "01/01/1990"; "")

    Fill right as necessary. A similar procedure could retrieve the ISIN numbers from a vertical column (as your sample data indicates).

          ISIN numbers in staggered columns