Sorry, I tried to keep my question simple, but let me now open up it more. I want to calculate a value of my stock portfolio for all days. I have end-of-day quotes in a table called HistoricalQuotes:
Ticker
| Date | Price |
| KO | 28.6.2007 | 128,06 |
| MSFT | 28.6.2007 | 328,06
|
| NOKIA | 28.6.2007 | 228,06 |
| KO | 2.7.2007 | 102,07 |
| MSFT | 2.7.2007 | 302,07 |
NOKIA
| 2.7.2007 | 202,07 |
| KO | 3.7.2007 | 103,07 |
| MSFT | 3.7.2007 | 303,07
|
| NOKIA | 3.7.2007 | 203,07 |
| KO | 4.7.2007 | 104,07 |
| NOKIA | 4.7.2007 | 204,07 |
| KO | 5.7.2007 | 105,07 |
| MSFT | 5.7.2007 | 305,07 |
| NOKIA | 5.7.2007 | 205,07 |
| KO | 6.7.2007 | 106,07
|
| MSFT | 6.7.2007 | 306,07
|
| NOKIA | 6.7.2007 | 206,07 |
| KO | 9.7.2007 | 109,07
|
| MSFT | 9.7.2007 | 309,07
|
| NOKIA | 9.7.2007 | 209,07
|
<colgroup><col span="3"></colgroup><tbody>
</tbody>
and in the other table I'm calculating my portfolio value. For the sake of simplicity I'm now assuming that I own same companies and same number of shares all the time. In reality this will vary, and an array of tickers and # of shares are calculated in another tab.
Date
| Portfolio value
|
| 2.7.2007 | 10 124,20 |
| 3.7.2007 | 10 184,20
|
| 4.7.2007 | 7 203,50
|
| 5.7.2007 | 10 304,20
|
| 6.7.2007 | 10 364,20 |
<colgroup><col span="2"></colgroup><tbody>
</tbody>
Portfolio value is calculated with the following formula:
=SUMPRODUCT({10;20;30};SUMIFS(HistoricalQuotes[Price]; HistoricalQuotes[Date]; $E2; HistoricalQuotes[Ticker];{"MSFT";"NOKIA";"KO"}))
If works perfectly, as long as there is a rate for the stock in HistoricalQuotes-table for a date when portfolio value is calculated. In case of MSFT 4th of July rate is (naturally) missing, as stock exchange is closed. So, my formula should use the previous day (3rd of July) rate for MSFT, when it's calculating portfolio value for 4th of July. I should use use date that is maximum of dates that are "<=" than the calculation date (here $E2).
I thought that I could use the following formula to calculate row numbers from HistoricalQuotes-table, which contain row-numbers that contain the correct price:
=(--(TRANSPOSE({"MSFT";"NOKIA";"KO"}) = HistoricalQuotes[Ticker]) ) * (--(HistoricalQuotes[Date]<=$E2)) * ROW(HistoricalQuotes)
=maximum( (--(TRANSPOSE({"MSFT";"NOKIA";"KO"}) = HistoricalQuotes[Ticker]) ) * (--(HistoricalQuotes[Date]<=$E2)) * ROW(HistoricalQuotes) )
{9;12;11}
If I got row numbers right, INDEX function would give me easily right price.
Unfortunately I have not been able to figure out how get the result I need.
