Exercise 1 – The Power of Compound Interest
Exam 3 Scaling Up with VBA
Page 1 of 6
Exam 3 – Scaling Up with VBA
Learning Objective: In this assignment, you will use your recently acquired knowledge
of Excel VBA coding to scale up the analysis from the previous exercise. You should be
able to see how powerful Excel and coding can be.
Inputs to be given in class on the spreadsheet template:
1. Ticker symbols: See the spreadsheet template
2. Starting and ending dates: See the spreadsheet template
Background/Motivation: In the previous FIT exercise, you learned how to code in VBA
using Excel to accomplish repetitive tasks using the Fo
Next commands. In that exercise,
you used only 2 companies. Now, let’s scale up the analysis to look at a more realistic
case where you want to do a similar analysis using all the companies (that we have good
data for) in the S&P 500 index as of the ending date in the spreadsheet template.
What makes this exercise more relevant than the previous exercise is:
1. We have more companies and therefore can find more opportunities for
investment.
2. We write the output to a worksheet with just 1 row per company based on the
most cu
ent prices (as of the download date).
3. We have an extra variable in the data set, “Moat” in column H of Sheet1.
a. “Moat” refers to the “Economic Moat” as defined by Morningstar and
obtained from its web site (via paid subscription). The term “Moat” was
popularized by Wa
en Buffett to describe the strength of competitive
advantages a company has. Just as in medieval times when a wide moat
around a castle better protected the inhabitants from the invading hordes,
so too, a “wide economic moat” around a company protects its ability to
earn high Returns on Invested Capital for long periods of time.
Morningstar is the only research service to my knowledge that publishes
moat ratings for companies.
4. Because of #2 and #3, we can end up sorting the results by Moat and then by
whether the stock is in an uptrend or not and whether the uptrend is reasserting
itself after a
ief (or not so
ief) rest.
Caveat: This exercise uses Technical Analysis to illustrate the use of VBA in Excel.
Many investors and Finance professors scoff at Technical Analysis with well-grounded
theory and empirical evidence that it does not work. Nevertheless, you will discover that
many investors still use it as a way of understanding the flow and trend of the market. For
your long-term money (e.g., retirement accounts), you are most likely much better off
employing asset allocation models and staying invested, focusing on low cost
investments (remember the portfolio optimization exercises and Jack Bogle’s quote).
That said, your ability to code and work with data will probably benefit you greatly in
your future career. It’s all about data in today’s world.
Exam 3 Scaling Up with VBA
Page 2 of 6
Procedure:
1. Launch Excel and a web
owser.
2. Download the spreadsheet template from Canvas.
3. Save the workbook. Important note: The file extension for the workbook is *.xlsm.
This means that the workbook is what is called a “macro-enabled” workbook. Do not
save it as a *.xlsx file. The VBA code only works in a *.xlsm file.
4. Be sure “Developer” is enabled on the Ri
on. Here’s how you do it.
a. Click “File”, then “Options”.
. Click “Customize Ri
on” on the left navigation panel.
c. In the “Main Tabs” box, click the box next to “Developer” and then click “OK”.
5. Start in Sheet1. Notice that Sheet1 contains the data. In this exercise, we have stock price data
for 456 companies.
a. Side note: These data were downloaded from Yahoo Finance using a program called
“YLOADER” obtained from https:
yloader.com/. YLOADER can download stock
prices in a “batch file”, which just means “all in one shot”. All you have to give it
is a list of tickers in a text file. You can explore the program on your own. Best of
all, YLoader is a free download (at least for now).
6. In Sheet1, there are 8 columns of data with no heading. Again, note that these are
WEEKLY stock price data. This means that you need to know what you getting when
you download data. Here are what the column headings would be. Note: Do NOT
include a header row as the VBA code does not account for a header row.
a. Column A: Date (this is the 1st trading day of the week, typically a Monday)
. Column B: Ticker
c. Column C: Open (1st traded price on Monday morning)
d. Column D: High (highest price during the week)
e. Column E: Low (lowest price during the week)
f. Column F: Close (closing price on Friday, 2:00 p.m. MT). Note: We are using
Yahoo’s “adjusted” closing prices in this exercise.
g. Column G: Volume (volume of shares traded during the week)
h. Column H: Moat (the economic moat as defined by Morningstar)
7. So, you may now be asking yourself: Why use weekly prices instead of daily prices?
Actually, this reflects my personal preference. Daily stock price charts are extremely volatile
and hard to read. There are many more false signals given off when using daily charts. My
investing style is to be a “slow money trader” rather than to flatter myself into thinking that I
can really be a “fast money trader” like the ones you see on CNBC. If you want to work with
daily stock price data, have at it. This code should work just fine with daily data with just a
little tweaking of initial conditions. Also, Excel can handle much more data than what we
have here.
https:
yloader.com
Exam 3 Scaling Up with VBA
Page 3 of 6
8. Go to Sheet3. Notice that Sheet2 has been deleted to avoid confusion with the previous
exercise. We are NOT going to re-write every trading week’s price and its co
esponding
moving average. The reason is SPEED of execution. With many companies, it takes several
minutes to do that and it’s just not worth the wait.
a. Instead of re-writing all the data out again, we will end up writing out just 1 row for each
company. After all, that is what we are really interested in – what the latest price is telling
us.
9. In this exercise, Sheet3 will be our output worksheet. So, go to Sheet3 and look at the
headers, which have been left in there on row 1. The titles will be written out again as part of
the code.
a. Column A: Ticker
. Column B: Date (this is the latest date in the dataset)
c. Column C: Closing Price (as of the last date in the dataset)
d. Column D: SMA (Simple Moving Average: 26-week SMA in this exercise as of the Date
in column B)
e. Column E: SMA Lagged (SMA 3 weeks ago)
f. Column F: SMA up? (TRUE or FALSE, based on whether SMA is > SMA Lagged or
not)
g. Column G: Pr cross over SMA? (TRUE or FALSE, based on whether Price 3 weeks ago
was < SMA Lagged AND Price now > SMA now, where “now” is the last Date)
h. Column H: Moat (the company’s Economic Moat)
10. Click “Developer” on the Ri
on and then click “Visual Basic”. A separate window will
open with the VBA code displayed.
11. Let’s walk through the sections of the code to see what you are looking at and what you need
to do to complete the exercise.
12. Sub and Dim statements
a. The “Sub” statement is always the first line of a VBA program. It stands for
“Su
outine”. That last line of the program is always “End Sub”.
. The “Dim” statement establishes the variable names and “dimensions” of memory space
to hold for the data. For example, closePrc(1000,1000) means that we can have 1,000
different companies each with 1,000 prices).
i) SMA(1000, 1000) means “Simple Moving Average” and we’ve allocated 1000 rows
and 1000 columns and the same with jDate(1000, XXXXXXXXXXThis is where program
planning and logic come in. I have set up the logic so that the ROW NUMBERS in
the SMA and jDate matrices co
espond to a company number and the COLUMN
co
esponds to a “date” number starting at 1. So, SMA(2,50) is the simple moving
average for Company 2 on date “50”.
ii) sumPrcsSMA: the sum of the prices in the simple moving average calculation.
iii) jCo: a counter for the company number
c. jRow: a counter for the physical output row
d. numCos: the total number of companies we have
Exam 3 Scaling Up with VBA
Page 4 of 6
e. SMAlength: the length of the SMA.
f. jTicker(1000): a variable for the ticker for 1000 companies maximum
g. jDate(1000,1000): the date for each price for each company. Each company can have
1000 prices, so there can be 1000 dates.
h. jMoat(1000): the economic moat for each company
i. jLag: the lag for which we want to compare the price to the SMA. For example, if we
choose 1, then we would be comparing the Price 1 week ago with the SMA 1 week ago.
13. Sheets("Sheet3").Range("A1:H2000").ClearContents: This statement clears the contents (but
not the formatting!) of the cells in the range A1:H2000 in Sheet3. This ensures that we start
with a blank worksheet in the output sheet.
14. Initialize variables: Set initial conditions.
a. numCos = 456 (the number of companies we have for our analysis. We use numCos in
the For statement to tell VBA how many loops to do)
. numPrcs = 257 (the number of prices we have for each company. It is much less
complicated to have the same number of prices per company. Plus, we need at least 26 to
form a moving average.)
c. jLag = 3 (how many weeks ago we will compare the price to the SMA for. We chose 3
weeks, meaning that you will need to write an IF statement later to test if Price is less
than SMA 3 weeks ago)
15. Read in all prices for all companies: This is the same code as in the previous exercise.
Therefore, you do not need to change anything.
16. Calculate the Simple Moving Average for all companies for all dates.
a. This is done in the same manner as in the previous exercise except that SMAlength is 26
weeks.
. You do not need to change anything here.
c. Remember that we are not writing out the week by week SMA and closing price for each
company to save processing time. I/O (Input/Output), but especially Output, operations
can take a long time to do.
17. Write the header (titles) in row 1 of Sheet3.
a. This code is also done for you.
18. Determine if SMA is in uptrend or downtrend and if the Closing Price has “recently” crossed
from below the SMA to above the SMA. We are defining “recently” as in 3 weeks ago vs.
“today”. (Note: “Today” is actually the end of the data set, which is 11/20/2020.
a. This is where your (possibly newly developed) coding skills will be put to the test.
. The basic structure of the Fo
Next loops and the If, Then, Else statements has been left
intact for you. You have to interpret the If, Then, Else logic and write it out as coding
statements.
c. The comments (instructions) have also been left in the code.
d. Syntax note: The results of If statements that result in TRUE or FALSE need to be put in
double quotes, as in “TRUE” or “FALSE”.
Exam 3 Scaling Up with VBA
Page 5 of 6
e. You will also notice that in Sheet3, columns F and G are “conditionally formatted” such
that TRUE is green and FALSE is red. Also, the Moat in column H is formatted so that
“Wide” is green and “None” and “Na
ow” are red.
19. Run the program by clicking the Run icon.
20. Check: There is an easy function to use Excel to check if your TRUE/FALSE results in
Columns F