Custom aggregation of Finance data in Google sheets

Question

I am currently working on a calculation to identify data trend on stock prices and is currently using google finance libraries for the same.

I would like to compute the following information in an automated way.

Percent of days with positive returns during the last x days
Standard deviation of the returns(price diff per day, not the actual stock price) for this period

I am currently placing the

GOOGLEFINANCE("GOOGL","price",WORKDAY( TODAY(), -200 ) , TODAY(), "DAILY")

in a separate sheet and calculating the differences per row by doing a lookup of the previous row and aggregating the data.

I have to update the ticker manually for every stock that I am interested in and then copy the summary information to master sheet.

Is there any automated way that I can compute this as single/configurable calculations ? Please share your thoughts.

share a copy of your sheet with example of desired output – player0 Feb 13 '20 at 12:12 — player0, Feb 13 '20 at 12:12

score 2 · Accepted Answer · answered Feb 13 '20 at 14:50

delete all in range C2:D and paste this in C2 then drag down:

=ARRAYFORMULA({SUM(IFERROR(IF(
 QUERY(GOOGLEFINANCE(A4, "price", WORKDAY(TODAY(), -200), 
 TODAY(), "daily"), "select Col2 offset 2", 0)>
 QUERY(GOOGLEFINANCE(A4, "price", WORKDAY(TODAY(), -200), 
 TODAY(), "daily"), "select Col2 offset 1", 0), 1, 0)))/COUNTA(
 QUERY(GOOGLEFINANCE(A4, "price", WORKDAY(TODAY(), -200), 
 TODAY(), "daily"), "select Col2 offset 1", 0)), STDEV(IFERROR((
 QUERY(GOOGLEFINANCE(A4, "price", WORKDAY(TODAY(), -200), 
 TODAY(), "daily"), "select Col2 offset 2", 0)-
 QUERY(GOOGLEFINANCE(A4, "price", WORKDAY(TODAY(), -200), 
 TODAY(), "daily"), "select Col2 offset 1", 0))/
 QUERY(GOOGLEFINANCE(A4, "price", WORKDAY(TODAY(), -200), 
 TODAY(), "daily"), "select Col2 offset 1", 0)))})

Custom aggregation of Finance data in Google sheets

1 Answers1