1

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

player0
  • 124,011
  • 12
  • 67
  • 124
JNMaven
  • 41
  • 5

1 Answers1

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you for your answer! I'll study it some more. I just realized the date series contain non-market days which causes the time series to have gaps. I've tried replacing the time series using `=INDEX(GOOGLEFINANCE(".INX","price",MIN('Stock trade data'!D$2:D),TODAY()),0,1)` (now placed in the "Historical V3" tab cell `L14`) so it automatically leaves out non-market days, but it breaks the `BYCOL` formula. I wonder why that is. – JNMaven Dec 31 '22 at 06:00
  • @JNMaven on V3 in M15 use: `=BYCOL(B2:J2, LAMBDA(y, INDEX(IFNA(VLOOKUP(INT(L15:L), LAMBDA(x, {INT(x), x}) (GOOGLEFINANCE(y, "price", OFFSET(y, 2, ), IF(OFFSET(y, 3, )="", TODAY(), OFFSET(y, 3, )+1))), 4, )))))` – player0 Dec 31 '22 at 12:49