Attached been sent
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 For/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 current 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 Warren 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 brief (or not so brief) 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 browser. 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 Ribbon. Here’s how you do it. a. Click “File”, then “Options”. b. Click “Customize Ribbon” 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) b. 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 corresponding 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 b. 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 Ribbon 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 “Subroutine”. That last line of the program is always “End Sub”. b. 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, 1000). This 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 correspond to a company number and the COLUMN corresponds 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) b. 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. b. 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. b. The basic structure of the For/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 “Narrow” 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