Layman tips- Calculating Daily Moving Averages for Stocks using GoogleFinance function on Google Spreadsheets
GoogleFinance function in Google Spreadsheets is a nice way to do your basic research on equity stocks.
Please see my earlier article on how to use Google Finance function.
After trying few basics, i wanted to calculate Daily Simple Moving averages i.e DMAs of the stocks. I read that 200 DMA , 50 DMA etc. are used by many investors.
So on searching, here is the function to be used in Google Spreasheets to calculate 200 DMA for a particular stock -
=AVERAGE(INDEX(GoogleFinance("NSE:CASTROLIND", "all",WORKDAY(TODAY(),-200),TODAY()), , 3))
This you can paste in the Cell of Google Spreadsheet where you want the 200 DMA to appear and press enter. The first input to the GoogleFinance function is the ticker of Castrol Inida stock of NSE.
On calculation (date-13.09.2021) , the value shown in spreadhseet is - 134.35 ! Please observe the digit "3" in the end, it calculates the DMA using High value of a Day, to get closing value - you have to use 5. Using "5" , the value is 132.37. Whereas searching online it shows - 132! Not far off.... You are also requested to check the DMAs of your favourite stock and let me know in the comments.
If you want to claculate 100 DMA or 50 DMA, simply replace the 200 in blue colour of above formula by respective number i.e 100, 50 or 20 .. regards!!!
Disclaimer - This information is just for basic understanding and not to be considered as a financial advise or suggestion. Please note that this article is by a layman to just share the discover he had come across.