google-sheetslambdagoogle-sheets-formulavlookupgoogle-finance

How to generate a time series of the historical performance of a stock portfolio on Google Sheets?

I'd like to generate a time series of the historical performance of a hypothetical stock portfolio on Google Sheets, considering there will be stock trades made at different times. The desired result would be a p/l curve that can be compared with a benchmark, e.g. the S&P500 index. The data of the stock trades is available in the "Stock trade data" tab.

In the "Historical" tab, I've pulled the historical prices of each stock position using GOOGLEFINANCE. The timeframe is from the day of opening the position to the day of closing it. For positions that are still open, the GOOGLEFINANCE formula will keep pulling the current prices everyday.

Currently, all historical time series begin at row 15 and since each time series begin at a different date, they're not synced with the dates at column A. Is there anyway each time series can be shifted to the right row so that the prices correspond to the dates in column A?

I appreciate anyone who can take a look at my spreadsheet (linked below) and see what's the best solution for this.

Yellow cells contain formula. Blue cells are manually inputted data. The unlocked sheet is editable.

https://docs.google.com/spreadsheets/d/1rwEiAQiU-NgDUhHtl_g1DJkQaVO45GD-AztJOjZJZO0/edit#gid=835050570

Thanks! J

enter image description here

Solution

  • try like this:

    =INDEX(IFNA(VLOOKUP(A15:A, LAMBDA(x, {INT(x), x}) 
 (GOOGLEFINANCE(B2,"price",B4,IF(B5="",TODAY(),B5+1))), 4, 0)))

    enter image description here

    all in one go:

    =BYCOL(B2:J2, LAMBDA(y, {"Close"; 
 INDEX(IFNA(VLOOKUP(A15:A, LAMBDA(x, {INT(x), x})
 (GOOGLEFINANCE(y, "price", OFFSET(y, 2, ), 
 IF(OFFSET(y, 3, )="", TODAY(), OFFSET(y, 3, )+1))), 4, )))}))

    enter image description here