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

Comments

Post a Comment

Popular posts from this blog

Challenges to "Over to Air (OTA)" updates for automotive vehicle

Image
  1 - The advantages and disadvantages of using OTA and why they adopted this function. OTA started with the first personal computers in late 90's with windows / macos , then moved to smartphones and then to the automotive world where Tesla was the first company followed by Mack trucks , GM , BMW etc. Now more and more companies are working on it and in future more devices will follow OTA updates. Regarding the automotive world, OTA brings the following advantages and disadvantages. Advantage : A : Efficiency - OTA updates are  more efficient way to fix bugs and update software than to manually update each individual device. OEM can seamlessly update the firmware to the whole fleet without physically checking. OTA updates also enable OEMs to more easily update software on devices that are difficult to access, such as ECU or dashboards or infotainment systems etc. B : Development timing reduction - OTA updates can catch issues before devices launch, which can save OEMs time an...
Read more

How India can move to digital economy ?

Image
Scope :  On 8 November 2016, the Government of India announced the demonetisation of all ₹500 and ₹1,000 banknotes of the Mahatma Gandhi Series.Of Rs 15.41 lakh crore (15.4 Trillion)  demonetised currency notes of Rs 500 and Rs 1,000 denominations, only Rs 10,720 crore (100 Billion) did not reach to the banks or the RBI.This means only 0.7 per cent of demonetised currency notes were junked in the exercise.  This showed how good idea executed poorly end up in disaster for poor people.  Demontisation was done to bring back all black money and let people more to digital transactions but as mentioned above , black money objective didn't achieve and amount of digital transaction increased during during that period but decreased with time. Action items :  Demonetize big currency notes like 1000 or 500 or 200. Only small currency notes should be in service ( 1 paise , 10 paise , 1 Rs , 10 Rs , 100 Rs Only ).  Enough time like 3months to 6months must be given for p...
Read more