• This service has been inactive since 2010 as my new role in TM1 consulting.
  • Please disregard any commercial offers as this website has been archived and transformed into a blog rather than a commercial website.
  • Please contact me for more info in BI and Financial Modelling consultancy.

Live-updated Beta Calculation

Assume CAPM is applicable and works empirically without any doubt in the theory, we can then calculate a stock Beta dynamically over time as it represents the updated historical stock prices from the latest price. Hence, the stock Beta changes over time.

The process and the VBA coding are similar with the Live-updated Yield posted before.

This spreadsheet consists of 3 worksheets that dynamically calculates the beta of Astra stock returns (ASII.JK) against the Jakarta Composite index returns (^JKSE) as being updated over time by clicking the "Update" buttons. Thus, we can get the latest Beta of Astra stock.

The first worksheet is JKSE sheet that contains the recent updated market prices and the second one is ASII sheet for the recent updated stock prices. In these two sheets, there are two "Update" buttons that allow us to update the recent prices from web query and to record the recent prices into the Beta sheet (third worksheet).

The stock Beta is then calculated by simply finding the slope between the periodical stock (x axis) and market (y axis) returns.

[Sorry, it's a premium spreadsheet]