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
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.