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()),,)

Date	Open	High	Low	Close	Volume
1-Feb	133.75	135.38	9-May	134.14	106239823
2-Feb	135.73	136.31	13-May	134.99	83305367
3-Feb	135.76	135.77	12-May	133.94	89880937
4-Feb	136.3	137.4	13-May	137.39	84183061
5-Feb	137.35	137.42	14-May	136.76	75693830

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

1-Feb

133.75

135.38

9-May

134.14

106239823

=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..