I have a list of ISINs.
我有一份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:
现在我得到公式，=BDH(A1)"px last";"01/01/1990";"") ISIN number的股票价格，在A1单元格中。但是，我的问题是我要退两列，一列是日期，一列是价格。所以我必须将isin与一个空单元格对齐。但是沿着每一列拉公式，只会得到第一批股票价格。我现在的结构是这样的:
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.
我的问题是，我需要手工将单元格A1更改为C1，否则我将得不到任何进一步的数据。
How should I structure my worksheet, to get the stock prices automatically and the output does not overlap?
我应该如何构造我的工作表，以自动获得股票价格和输出不重叠?
1 个解决方案
#1
1
You can retrieve the first, first, third, third, fifth, fifth, etc columns from across row 1 with a little maths.
你可以用一些数学方法从第1行中检索第1、第1、第3、第3、第3、第5、等等列。
=INDEX(1:1, 1, INT((COLUMNS($A:A)-1)/2)*2+1)
This would replace A1 in your first column formula.
这将替换第一列公式中的A1。
=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数字(如示例数据所示)。
#1
1
You can retrieve the first, first, third, third, fifth, fifth, etc columns from across row 1 with a little maths.
你可以用一些数学方法从第1行中检索第1、第1、第3、第3、第3、第5、等等列。
=INDEX(1:1, 1, INT((COLUMNS($A:A)-1)/2)*2+1)
This would replace A1 in your first column formula.
这将替换第一列公式中的A1。
=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数字(如示例数据所示)。