An ad-free and cookie-free website.
Videos are available at one organized Quant 101 Playlist on YouTube.
Install Excel Data Analysis ToolPak and Solver Add-In (17:27)
Welcome. Today's goal is to set up our Excel system and cover other helpful topics related to using a spreadsheet for data analysis.
I'm Paul, and I get frustrated by how long it takes to get systems ready before you can get to the fun part, analyzing data.
So here we will walk through the considerations for different operating systems and Excel versions, going step-by-step, to save you time.
If this is your first time with us, this tutorial sits within a series of 30 financial modeling tutorials, called Quant 101. The way it works is I ask you to duplicate all of these models on your system as a way to learn fairly advanced Excel skills and to learn about modeling stocks. So if you are sticking around for that, I will also cover how to download and import a free data set used throughout the series.
For those watching the video on YouTube, if you want to slow all of this down and see the instructions in print, everything I say here is duplicated on a web page. The first link in the video's Description section goes straight to it.
Let's start with an overview.
First, we will run through advantages and disadvantages of spreadsheets for financial modeling.
Second, we review my setup and cover appropriate versions of our spreadsheet of choice, Excel, for Windows and Mac users.
Third, we will install the Data Analysis ToolPak for statistical analysis and the Solver Add-in for optimization.
Fourth, we cover the one dataset we use throughout the playlist and how to freely download it so you can get started right away.
Fifth, I'll quickly discuss what institutional investors use instead of spreadsheets, meaning statistical programming software and their advantages and disadvantages.
And in our next episode we will review the three methods for calculating returns on stocks.
Before we get started with the technical part, let's take a moment to cover the advantages and disadvantages of spreadsheets which will help us later in our discussion of comparisons with the programming world.
First, for advantages, spreadsheets are:
Now, for the disadvantages, spreadsheets are:
So why are we using Excel?
First, a spreadsheet is helpful for organizing and visualizing our analysis, and for learning the concepts before moving on to programming, should that be the path you choose.
Microsoft Excel still leads in market share over Google Sheets and Apple Numbers, however the lead is narrowing.
Any search for technical jobs in the field of Finance will generally require some Excel experience, so this is our spreadsheet of choice.
Let's move on to the versions of Excel and operating systems.
On my end, I have a Windows 7 operating system with a local installation of Excel 2010. This obviously isn't the latest version, but any later version should suffice.
For the Mac, common Excel versions include 2011 and 2016. For Windows, currently the versions are named 2010, 2013 and 2016.
Some of the functionality across versions may not be exactly the same, but will be close. Other popular spreadsheet options, like Apple Numbers and Google Sheets are available, but here we won't take the time to translate functions from one to the other.
Okay, let's get these Add-Ins installed.
Everyone should run through steps one to four, and if you don't have the necessary Add-Ins installed already, then we will move to Step 3b.
Datamenu and if you see
Solveryou are good to go.
Now, let's move on to step 3b, and a quick note, the Add-In name for the
menu item is called Analysis Toolpak. It provides a
variety of statistical functions; including, analysis of variance,
correlation, random number generation, regression, t-tests and a
The menu item
Solver is installed
using the Add-In named Solver Add-in. We will use it
to find a spot on a curve, with x-and-y coordinates, that maximizes the
ratio of return over risk, for a portfolio through a technique called
Now if you don't have these pre-installed on your version of Excel, follow these steps.
Manage:with the dropdown
Excel Add-Ins, then the
Solver Add-In, then hit the
Datamenu item and you should see them.
Let's move on to Step 4 to get that 61-row and 7-column data set.
Here, we need to download the data file and create a data tab called Returns. The goal will be to include this data tab with each Chapter file. So basically, you will carry it along with you, attaching the same tab to each spreadsheet, which will prevent us from having long references to other files. Also, creating links and troubleshooting will be much easier.
I should mention this is an available data set with four stocks already selected. Later, in Chapter 2, I will show you how to generate returns should you want to customize your own data set.
For now, to get the sample data set, follow these steps:
Save File As..., which may be worded differently depending on your browser.
Openand then select the file
returns.txtto open the Text Import Wizard.
Delimitedand then the
Tab-delimitedand then click
Dateand the format MDY identifies the month, day and year. Then click
Finish. This creates a new file that you can save using
Save Asin case there is a problem later.
Ctrl-c, then navigate to the newly created tab, going to cell B6, then use the shortcut
Ctrl-vto paste the data. Starting at B6 is important because as you follow along with what I'm doing it will save you time to keep the same cell numbers. Okay, we're all set.
template.xlsx. So each time we start a new Chapter, we can just make a copy and the Returns tab is already there for us. This saves us from having links across multiple spreadsheets, which I have found to be more difficult to manage, plus we will run some unique calculations on the tab depending on which Chapter file we are working on.
One more note about this data. If you noticed, all returns are in decimal notation. We have to be consistent, or down the road our math will get messy. So we will always keep the data in decimal format, and use cell formatting to present it. So a return that looks like 0.01, is the same as 1%, but we'll always keep it as 0.01. In Excel, you can always visualize it how you like.
Next, I want answer a question that comes up. Why not teach the series with programming instead of a spreadsheet?
Earlier we covered advantages and disadvantages of spreadsheets. This series intentionally uses a small data set so we can benefit from several of those advantages from earlier. This means we can visualize and work with the data to learn the concepts, without having to write code.
In the real world this is done with a statistical analysis package like MatLab, R and Stata, or straight in traditional programming languages like Python, Java or C.
Our sample data set covers returns for four stocks, an index and Treasury Bills over monthly periods for five years. Imagine how large the data set would be if you covered 5,000 stocks and daily returns, and this leads us to a discussion of the advantages and disadvantages of programming, because if you want to take your knowledge further, to the Institutional level, then understanding how this is done at scale is important.
Programming has the follow advantages.
Of course programming also has disadvantages.
So to summarize, we discussed spreadsheets and why they're well-suited for our first exploration of quantitative equity portfolio management.
We discussed Excel, set up the required Add-Ins then imported a sample data set and created a template for future chapters.
Then we closed with what our next steps could be, with programming, should you have a passion for portfolio management and want to take your career to the next level.
In the next episode we will start Chapter 2, with our Returns tab in place and start walking through 3 methods for calculating return: arithmetic, geometric and logarithmic.
Feel free to join us at any time, and have a nice day.
Don't lose sight of this valuable resource, subscribe to the email list below.
A newly-updated free resource. Connect and refer a friend today.