Click to Download Workbook:
Sometimes it is useful to compare stocks (or other assets) on a fundamental level. What I mean by this is let’s say we take every company in the technology sector within the S&P 500. We calculate the P/E for each and take an average to get the Sector Average. We can compare the individual asset we are examining to its respective sector in terms of this fundamental measure and get an idea on its valuation respective to its peers. We can also do this with the asset’s respective industry as well. Sometimes the average number is desirable, but I would tend to shy away from using an average here. There could potentially be an outlier with an astronomical P/E within the sector, thus the average P/E would be abnormally high. You are better off taking the median (middle value) in this case. Sometimes it is useful to compare stocks (or other assets) on a fundamental level. What I mean by this is let’s say we take every company in the technology sector within the S&P 500. We calculate the P/E for each and take an average to get the Sector Average. We can compare the individual asset we are examining to its respective sector in terms of this fundamental measure and get an idea on its valuation respective to its peers. We can also do this with the asset’s respective industry as well. Sometimes the average number is desirable, but I would tend to shy away from using an average here. There could potentially be an outlier with an astronomical P/E within the sector, thus the average P/E would be abnormally high. You are better off taking the median (middle value) in this case.
P/E is typically calculated as (Current Price of Stock/TTM Earnings)This P/E ratio can be represented with the following statement: “Investors are willing to pay 30 dollars for every one dollar of earnings the company makes”, if the P/E ratio of a company was 30 that is.
We can also do this on a historical basis if we had access to historical P/E data from a service such as Bloomberg. We would do this in order to understand the historical valuation of the sector, thus computing a historical average P/E. We could compare the current average P/E to the historical number in order to see if the sector may be overvalued at this point in time. Albeit, an event like 2008 or 1999 may throw off our average.
Wouldn’t it be nice if we could automate this task? Well, it turns out we can. We will use the Yahoo Finance API in order to obtain this data. Albeit, you can use other API’s, web scrape, or used paid services to obtain the data. I personally think using the Yahoo API is the easiest method. The quality of the data is sub-par, but it will do for this tutorial. Just a heads up, the Yahoo Finance API for historical data is currently defunct. This means you won’t be able to request this data from Excel.
In terms of how this workbook retrieves the data, each of these desired codes is inserted within a URL and requested within VBA. To give you an idea, here is an excerpt of the code where we use a for loop to iterate through all the tickers. The field values are being indexed in the first row when the user selects from the data validation lists. We simply take the value of the cell there and insert it as a string argument within our URL.
Dim y, z As Integer
For y = 3 To lastRow
Dim ticker As String: ticker = ws.Range(Cells(y, 6), Cells(y, 6)).Value
For z = 7 To 16
field = Sheets("Sheet1").Cells(1, z).Value
Dim URL As String: URL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=sn" & field & "hg"
Dim x As New WinHttpRequest
x.Open "GET", URL, False
Dim Resp As String: Resp = x.responseText
Dim lines As Variant: lines = Split(Resp, Chr(10))
Dim sline As String
Dim values As Variant
For i = 0 To UBound(lines)
sline = lines(i)
If InStr(sline, ",") > 0 Then
values = Split(sline, ",")
ws.Cells(row_count, col_count).Value = values(UBound(values) - 2)
col_count = col_count + 1
col_count = 7
row_count = row_count + 1
We could work with arrays instead of iterating through cell values, but I decided it would be simpler referring to two ranges of cells instead. The indexes are in the first row starting at cell G1. I highlighted the index formula below.
Disclaimer, this workbook is not meant to handle requests for many tickers (i.e., 50-100+) The reason being is that Excel is quite slow and breaks down the response from the API in a string format. Using the immediate window in Excel (CTR-G in VBA) we can examine our variables with a Debug.Print statement. Our response looks like this:
Essentially, we delimit the response from the API by a comma and extract the desired portion we need. In this case, we start at the second element in the string. The first request to the API is the company name of AT&T. The second is the last trading price. Again, these are the second element of the string since the string count starts at 0.
Lets put this workbook to use and try calculating some averages for the S&P 500 Telecommunications Sector. To start, We input the desired ticker symbols in the designated location. Don’t worry about symbol notation, (i.e., BRK/B) the workbook formats characters automatically.
Below are the results of the requests and calculations. Notice the disparity between the median and the average.
Below is a complete list of the various arguments we can use in order to obtain the data we want. Another heads up, some real-time fields may return NaN values (not a number). In this case, we would want to select codes that represent various metrics we want to be compared.
|m6||% Change From 200 Day Moving Average|
|m8||% Change From 50 Day Moving Average|
|t8||1-Year Target Price|
|m4||200-Day Moving Average|
|m3||50-Day Moving Average|
|c8||After Hours Change (Real-time)|
|a2||Average Daily Volume|
|c||Change & Percent Change|
|m5||Change From 200 Day Moving Average|
|m7||Change From 50 Day Moving Average|
|k4||Change From 52-Week High|
|j5||Change From 52-Week Low|
|p2||Change in Percent|
|k2||Change Percent (Real-time)|
|m2||DayÕs Range (Realtime)|
|w1||DayÕs Value Change|
|w4||DayÕs Value Change (Realtime)|
|r1||Dividend Pay Date|
|d||Dividend per Share|
|e||Earnings per Share|
|e7||EPS Estimate Current Year|
|e9||EPS Estimate Next Quarter|
|e8||EPS Estimate Next Year|
|g6||Holdings Gain (Real-time)|
|g1||Holdings Gain Percent|
|g5||Holdings Gain Percent (Real-time)|
|v7||Holdings Value (Realtime)|
|l1||Last Trade (Price Only)|
|k1||Last Trade (Real-time) With Time|
|l||Last Trade (With Time)|
|d1||Last Trade Date|
|k3||Last Trade Size|
|t1||Last Trade Time|
|j3||Market Cap (Real-time)|
|j1||Market Capitalization ($B)|
|i5||Order Book (Realtime)|
|r2||P/E Ratio (Real-time)|
|k5||Percent Change From 52-Week High|
|j6||Percent Change From 52-Week Low|
|p6||Price / Book|
|r6||Price / EPS Estimate Current Year|
|r7||Price / EPS Estimate Next Year|
|p5||Price / Sales|