CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-finance
pknpkn21
pknpkn21

Reputation: 23

Custom aggregation of Finance data in Google sheets

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.

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.

Upvotes: 2

Views: 332

Answers (1)

player0
player0

Reputation: 1

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)))})

0

Upvotes: 2

Related Questions