How to calculate monthly std deviation of daily returns in google sheets using google finance api

1
votes

how to calculate monlthy std diviatoin of daily returns in google sheets using google finance api? I think we need to use QUERY()but I am not sure how to form single data set in QUERY() by calling googlefinance() two times So far : My stock symbols are in Column A in sheet . I want to calculate monthly volatility as mentioned in https://www.investopedia.com/articles/investing/102715/computing-historical-volatility-excel.asp

I want have a colum that has monthly history volatility against the symbol in colum A :

=QUERY(QUERY( { ARRAY_CONSTRAIN(GOOGLEFINANCE("NSE:"&A2, "close", workday.intl(TODAY(),-5), workday.intl(TODAY(),-1), "daily"), 4, 2), ARRAY_CONSTRAIN(GOOGLEFINANCE("NSE:"&A2, "close", workday.intl(TODAY(),-6), workday.intl(TODAY(),-2), "daily"), 4, 2) }, "select Col2", 1), "offset 1",0)

With above query, I am able.to get data range which has last N day price in 1st colum and prev day price in colum 2. I would like to use and extend this to calculate monthly volatility. I am not sure how to use this price data range to calculate logarithm and and next calculation.

Thanks

1
google-sheetsgoogle-finance
Can you give an example of what you're looking for? Or a sample sheet? – Rafa Guillermo
there is no shortcut way to do this. you have to do a lot of math. – MattKing
Edited question. Thanks – prashantkumar dhotre

1 Answers

0
votes

This may be an over-simplification of what you're trying to achieve, but can you use the STDEV function on a range of closing values during a month?

=stdev(query(googlefinance("NSE:"&A2, "close", MonthStartDate, eomonth(MonthStartDate,0), "daily"),"select Col2 offset 1",0))

MonthStartDate would be your cell 'named range' containing your month start. Obviously, days, where the markets are closed, would not bring back a value and the STDEV would reflect this.

If the closing price is too simplistic and you need other attributes, then you could combine them in the query.