Calculating Volatility on a Basket of Stocks

Disclamer: I’m going to come right out and say it: This blog is not about investing. Everything in this post is about my personal investing style and is not intended as investment advice. I am NOT qualified to provide it to you. This post is about how to use tools you have lying around your computer to accomplish specific goals and the approach I have taken to accomplish one of them. With a whack of meandering thoughts about the project in general. Keep that in mind when reading this post. The take away is that there’s always a way to make a computer do what you want. It is not that this solution is right for your financial needs. Talk with your financial planner. They’re the experts *.

I love investing. I love economics. I don’t have a background in it but I think if I could go back in time and change my career focus I would have been a really good financial planner. The whole world speaks to me. I am a well read amateur in the discipline and I do okay. There’s two individuals that I emulate in my approach to making money on the stock market: Warren Buffett and Norman Rothery. Yeah, I know. Who doesn’t pick Warren Buffett. But let’s talk about Norman Rothery. He’s the founder of the stingyinvestor and he contributes to the Globe and Mail’s investing section with a collection of portfolios to serve as a starting point/suggestion for investing. I’m a HUGE fan of the concepts presented in his columns. His portfolios, back tested and theoretical for sure, tend to do pretty well. I have long wanted replicate his portfolio ideas into my own retirement plan. Dr. Rothery’s portfolio that really speaks to me is the Stable Dividend Portfolio. It takes the 300 largest companies on the Toronto Stock Exchange (oh yeah! I’m Canadian, btw), discards all of the companies that don’t pay a dividend and then sorts them by one year average volatility. Buy equal amounts of the 20 with the lowest one year volatility and rebalance on a monthly, quarterly, or yearly basis and Robert’s your mother’s brother: You have a pretty decent portfolio that consistently beats Canadian market indexes. I like this. Simple. Dividends! Repeatable.

So here’s what I need:

• The Top 300 stocks on the Toronto Stock Exchange based on market cap
• The dividend yield for each stock – removing any that don’t pay a dividend (~200 stocks)
• The average annual volatility for each stock

Here’s how I went about getting that data.

The Bloomberg Problem

The problem: Most of his screens are based on data and ratios that are not available in commonly available stock screeners. Rothery’s data is based on Bloomberg data.

So I dived into how I could generate the list of stocks without having to rely upon Dr. Rothery’s publishing schedule. Why not simply take it from his Globe and Mail posts? Sure. I could just look at the portfolios he publishes and duplicate them. That’d be easy. But what if he retires? What if something changes? If this is going to be a significant part of my portfolio I need to not rely on what someone else publishes or when. It is important to me that I can maintain this approach for years to come. Hopefully many many many years. And in trying to figure out how to replicate his portfolio with my own data I started to run into problems.

The first is that I haven’t been able to find a stock screener that shows the average one year volatility in the same way that Dr. Rothery uses. His published articles indicate that he uses Bloomberg data. Maybe Bloomberg has this as a data point but it’s not anywhere else that was easy to find. I didn’t want to pay for a Bloomberg subscription quite yet. I don’t have the assets to justify it. so I tried to find a screener that did to no avail. I tried:

• Yahoo Finance
• Interactive Brokers
• Globe and Mail
• Questrade
• Finviz
• Seeking Alpha
• TMX Money
• Investing.com
• …

Yeah. Many, many, lots. None of them had the volatility calculation that was used to generate the portfolio. Boo. There was a second problem – and we will get into that a bit later: filtering by instrument type was iffy at best.

Here’s the rub. Calculating average volatility is SUPER EASY.

• Take the closing price history of a stock over the last 365 days (which provides you with ~252 data points)
• Generate the log of the daily returns.
• Calculate average daily returns
• Calculate standard deviation
• Annualize the volatility.

Yes. Google rocks. We’ll get into how this is implemented in the “Solution Section”. So I have what I need to do in order to get the average one year volatility. Superb. I can do this! It’s just math and spreadsheets are super good at this. I don’t need a Bloomberg terminal. All I need to do is create a system that takes historical price data and outputs volatility. Easy peasy. Alas. This was not to be.

The Search for Closing Prices

One would think that the closing prices of stocks would be pretty easy to get. It is. But only if you strike upon the right solution early. I wanted to be fancy and use apis and coded solutions ’cause it’d be cool. And Github friendly. I found an api, Alpha Advantage that let me download price history for a list of stocks. Yay! It worked amazingly. I wrote a python script to parse the price history and generate a list of stocks that fit the criteria. A month later they put a cap on the number of requests per day for a free account. 25. I could only make 25 requests per day without a paid subscription. That’s not a lot. And it broke my python script so much I left this quest alone for a year.

Don’t get me wrong. I’m all for paying for services. If my needs were greater I might have done it. But a ROI calculation (it cost more than I’d make at this point) indicated that this was not the route for me. I kept searching until I stumbled upon something really interesting:

=StockHistory()

Microsoft excel, if you have a M365 subscription – or maybe if you’ve paid for it, has a function called StockHistory. OMG! Best thing ever. Enter this function into a cell with a ticker symbol and some properties and it’ll output the stock price history for a year. EXACTLY what I need for my calculations. It even supports defining the exchange market (more on that, I promise). The output is raw, meaning it doesn’t recalculate based upon dividends, but that’s okay. We’re going for volatility so massaging the data doesn’t matter for dividends because we only care about the price change on a daily basis.

So now I have the ability to grab the historical prices of a stock over the course of the year without having to utilize an API that has a subscription fee. I can use this historical price information to generate the volatility of ANY stock ticker supported by excel. And since Excel supports the Toronto Stock Exchange we are well on our way to having this working.

Dividend Yield Missing

So one would think I could just use the excel data and be done with it. Alas. No. I could not grab the dividend yield using excel. There’s documentation on the web that indicates it works but it doesn’t. There’s no [Dividend] value. This may be because I’m pulling Canadian stocks but since it’s not an option I didn’t dive into finding out if Excel can also provide market cap.

Instruments

This was a tricky one and ultimately ended up being ridiculously manual. What I needed was 300 tickers from the Toronto Stock Exchange sorted by market cap. You’d think this would be easy. It’s actually not. Most screeners don’t allow for instrument type filters so you’d end up with closed end funds, REITs, warrants, US stocks, etc. I tried so many iterations of making this automated. It just didn’t work. So here’s what I did instead:

I use Quicken Classic. I know. I’m dating myself. But it does have a pretty good watchlist system. I entered every ticker into Quicken as a watchlist item that met the following criteria:

• Member of the Globe and Mail 250 Top Stocks for 2025 (which ever year you’re going with).
• Listed on CompaniesMarketcap.com.

Am I missing some. Yeah. Probably. But it did generate a list of 362 stocks. Once I ran update in Quicken, the stock stats were updated and then I created a report that included:

• Watchlist items
• Market Cap
• Quote
• Dividend Yield

The good news is that I can always add more to the Quicken watch list so if I discover I’m missing a security I can add it in the future.

I can now export the report into excel and move forward with the process.

Fun With Excel

Okay. Now I have a list of stock from the Toronto Stock Exchange in Excel with the name, dividend yield and market cap. Superb. The market cap and dividend yield are two of the three requirements for making this screen a reality. I removed any columns or rows that are extraneous which leaves me with a format like this:

Security Name

Ticker

Quote/Price

Market Cap

Dividend Yield

Dividend Yield and Market Cap are provided by Quicken. So is the Quote/Price but I don’t care about that. Now what we need to do is take this table, generated by Quicken, to lookup the price data of each of the ticker symbols, and then generate the average volatility. I delete any ticker that doesn’t have a dividend. And then I need to create a worksheet for each ticker that generates the volatility info. This is where VBA comes in.

Here’s the VBA script that does the following:

• Creates a new worksheet for every ticker symbol.
• Uses the STOCKHISTORY function to grab the stock price history
• calculates the average volatility based on the stock prices.

Sub CreateSheetsAndFormulas()
    Dim wsMaster As Worksheet
    Dim wsNew As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim l As Integer
    Dim sheetName As String
    
    ' Set the main sheet with your list/table
    Set wsMaster = Sheets("Report")
    
    ' Find the last row in Column A to know how many sheets to create
    lastRow = wsMaster.Cells(wsMaster.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through the rows to create sheets
    For i = 2 To lastRow
        sheetName = wsMaster.Cells(i, 2).Value
        
        ' 1. Create the new worksheet
        Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
        
        ' 2. Name the sheet (with error handling for invalid names)
        On Error Resume Next
        wsNew.Name = Left(sheetName, 31) ' Sheet names limited to 31 chars
        If Err.Number <> 0 Then
            MsgBox "Error: Could not name sheet " & sheetName & ". It might already exist or contain invalid characters."
            Err.Clear
            Application.DisplayAlerts = False
            wsNew.Delete
            Application.DisplayAlerts = True
            GoTo NextIteration
        End If
        On Error GoTo 0
        
        ' 3. Add Headers
        wsNew.Range("A1").Value = "XTSE:" & sheetName
        wsNew.Range("B1").Formula = "=STDEV.S(C4:C252) * SQRT(252)"
                
        ' 4. Add Sample Data/Formulas
        wsNew.Range("A2").Formula2 = "=STOCKHISTORY(A1,TODAY()-365,TODAY(),0,1,, 0,0,1)"

        For l = 4 To 252
            wsNew.Range("C" & l).Formula = "=LN(B" & l & "/B" & l - 1 & ")"
        Next l
            
        wsNew.Columns("A:C").AutoFit

Running this macro will create a worksheet for each of the ticker symbols in the main worksheet and calculate the average volatility. It takes about 30 minutes to run the macro but at the end you’ll have the data you need to move on to the next step.