问题描述:

I tried to summarize the idea of my question in the title, but it sounds a lot more complicated than it really is...

For anyone that doesn't know, Bloomberg is a data service provider where you can extract prices for various financial assets (and other things) into MS Excel. The format is quite typical: data is usually arranged in a time-series with dates progressing down rows and various financial products progressing across the columns.

Example: Historical prices for the S&P500 and DAX will appear in columns B and C,

respectively. The date for each price will appear in column A.

A B C D

DATE S&P500 DAX 'Formula'

02-Jan-14 1,462 7,778 = B * C

03-Jan-14 1,459 7,756 = B * C

04-Jan-14 1,466 7,776 = B * C

As time passes, the list gets longer with new prices, in this case the next price

for 05-Jan-14 would appear (actually the 7th is a Monday).

The problem arises when I have an Excel formula in column D that refers to either columns A, B, or C. As the days progress and the list of prices/dates grows longer (i.e. more rows), the formula in column D doesn't grow with this growing data set.

Is there a way in which MS Excel 2010 recognizes a dynamically sized data set from Bloomberg and automatically propagates referring formulas in adjacent columns downward (without using VBA)?

网友答案:

I can think of two possible ways that you could approach this. First, you could write your formula in a way that won't return anything if there is no date from the Bloomberg call in that row. For example, in your example your formula could be

=IF(A3="","",B3*C3)

You could then paste this down much further than your data will go. Even if the B3*C3 piece of the formula is costly, I don't believe it will slow your sheet down much as excel shouldn't even try to evaluate that piece of the formula unless there is a date in that row.

The other option would be to use VBA. You can take a look here for a relatively simple example.

相关阅读:
Top