Comparing Company Fundamentals With Excel and the Yahoo Finance API

Click to Download Workbook:
Querying Yahoo

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.

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
t81-Year Target Price
m4200-Day Moving Average
m350-Day Moving Average
k52-Week High
j52-Week Low
w52-Week Range
c8After Hours Change (Real-time)
g3Annualized Gain
aAsk
b2Ask (Real-time)
a5Ask Size
a2Average Daily Volume
bBid
b3Bid (Real-time)
b6Bid Size
b4Book Value
c1Change
cChange & Percent Change
c6Change (Real-time)
m5Change From 200 Day Moving Average
m7Change From 50 Day Moving Average
k4Change From 52-Week High
j5Change From 52-Week Low
p2Change in Percent
k2Change Percent (Real-time)
c3Commission
c4Currency
hDayÕs High
gDayÕs Low
mDayÕs Range
m2DayÕs Range (Realtime)
w1DayÕs Value Change
w4DayÕs Value Change (Realtime)
r1Dividend Pay Date
dDividend per Share
yDividend Yield
eEarnings per Share
j4EBITDA
e7EPS Estimate Current Year
e9EPS Estimate Next Quarter
e8EPS Estimate Next Year
qEx-Dividend Date
f6Float Shares
l2High Limit
g4Holdings Gain
g6Holdings Gain (Real-time)
g1Holdings Gain Percent
g5Holdings Gain Percent (Real-time)
v1Holdings Value
v7Holdings Value (Realtime)
l1Last Trade (Price Only)
k1Last Trade (Real-time) With Time
lLast Trade (With Time)
d1Last Trade Date
k3Last Trade Size
t1Last Trade Time
l3Low Limit
j3Market Cap (Real-time)
j1Market Capitalization ($B)
iMore Info
nName
n4Notes
oOpen
i5Order Book (Realtime)
rP/E Ratio
r2P/E Ratio (Real-time)
r5PEG Ratio
k5Percent Change From 52-Week High
j6Percent Change From 52-Week Low
pPrevious Close
p6Price / Book
r6Price / EPS Estimate Current Year
r7Price / EPS Estimate Next Year
p5Price / Sales
p1Price Paid
s6ÊRevenue
j2Shares Outstanding
s1Shares Owned
s7Short Ratio
xStock Exchange
sSymbol
t7Ticker Trend
d2Trade Date
t6Trade Links
vVolume

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *