Sunday, 7 February 2021

GOOGLEFINANCE : Function Usage to create Googlesheet Stock Protfolio

Lower Bollinger band Value : 

=average(query(sort(GoogleFinance("NASDAQ:AAPL","close", TODAY()-320, TODAY()),1,0),"select Col2 limit 20 "))-STDEV(query(sort(GoogleFinance("NASDAQ:AAPL","close", TODAY()-320, TODAY()),1,0),"select Col2 limit 20 "))*2

Upper Bollinger band Value : 

=STDEV(query(sort(GoogleFinance("NASDAQ:AAPL","close", TODAY()-320, TODAY()),1,0),"select Col2 limit 20 "))*2+average(query(sort(GoogleFinance("NASDAQ:AAPL","close", TODAY()-320, TODAY()),1,0),"select Col2 limit 20 "))

All Values :

=INDEX(GoogleFinance("NASDAQ:AAPL","All",WORKDAY(TODAY(),-5),TODAY()),,)

DateOpenHighLowCloseVolume
1-Feb133.75135.389-May134.14106239823
2-Feb135.73136.3113-May134.9983305367
3-Feb135.76135.7712-May133.9489880937
4-Feb136.3137.413-May137.3984183061
5-Feb137.35137.4214-May136.7675693830

=INDEX(GoogleFinance("NASDAQ:AAPL","All",WORKDAY(TODAY(),-5),TODAY()),2,)

1-Feb133.75135.389-May134.14106239823

=INDEX(GoogleFinance("NASDAQ:AAPL","All",WORKDAY(TODAY(),-5),TODAY()),,2)

Open
133.75
135.73
135.76
136.30
137.35

50 day Simple moving average (50SMA)

=average(query(sort(GoogleFinance("NASDAQ:AAPL","close", TODAY()-320, TODAY()),1,0),"select Col2 limit 50 "))

% up or down within 45 days

=1-MAX(INDEX(GoogleFinance("NASDAQ:AAPL","All",WORKDAY(TODAY(),45),TODAY()),,5))/GOOGLEFINANCE("NASDAQ:AAPL","price")

% Gain in 1500days

=GOOGLEFINANCE("NASDAQ:AAPL","price")/MIN(INDEX(GoogleFinance("NASDAQ:AAPL","All",WORKDAY(TODAY(),-1500),TODAY()),,5))

% Volume today :

=((GOOGLEFINANCE("NASDAQ:AAPL","volume")/1000)/(average(query(GOOGLEFINANCE("NASDAQ:AAPL","Volume",today()-30,today()),"Select Col2"))/1000))-1


** One can create MACD , RSI and Hekin Ashi with google sheet for multiple stocks.
Check next post..

No comments:

Post a comment