CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-finance
Mauro
Mauro

Reputation: 337

GoogleFinance Beta on a specific date?

How can I get the Beta for a stock on a specific date? I can get the current day's beta using the following

=GoogleFinance("BB","beta")

however, this formula does not work on specific dates

=GoogleFinance(B2,"beta", "2020/12/17")

Note: I'm open to using means other than GoogleFinance (Ex. Excel, websites, 3rd party extensions....) to achieve this.

Upvotes: 0

Views: 5047

Answers (1)

player0
player0

Reputation: 1

beta is not supported directly in google sheets for a historical date.

you need daily or weekly closed values for 3 or 5 years based on what beta variant you want to calculate

=GOOGLEFINANCE("BB", "close", 2020-5&"/12/17", "2020/12/17", "weekly")

and then input it into this equation:

β = cov(BB, SP500)/var(SP500)

googlefinance does not support monthly so for that you need to use:

=QUERY(SORTN(SORT({
      GOOGLEFINANCE("BB", "close", 2020-3&"/12/17", "2020/12/17", "weekly"), 
 TEXT(GOOGLEFINANCE("BB", "close", 2020-3&"/12/17", "2020/12/17", "weekly"), 
 "mm/yyyy")}, 1, 0), 9^9, 2, 3, 0), 
 "select Col1,Col2 order by Col1 asc", 1)

Upvotes: 4

Related Questions