Who: This series of posts is for people who have been dying to do some analysis of their investments using real financial data and Microsoft Excel. This post will be beneficial for those who have an average knowledge of Excel and a basic understanding of high school mathematics concepts.
What: This is the first post in a series about how to actually calculate some ratios and create metrics that will allow you to make sound investment decisions. The theory is covered elsewhere and this is a practical application! This post will discuss downloading data from Yahoo! Finance into Excel and getting the data ready for manipulation.



Where Do I Get The Data?

There are many places from which you can download financial data. Some sources are free while others charge money and some are simple to use while others are quite complicated. For the purposes of this tutorial, I will be using a free and simple source: Yahoo! Finance.

So How Do I Start?

The first thing you will want to do is select some stock tickers that you’d like to research. In this example, I’ll use a mutual fund with ticker RYVPX (I do not advocate or discourage the ownership of this fund, yet). RYVPX is Royce Value Plus Service mutual fund.

Step 1: First, you’ll want to proceed to yahoo finance at http://finance.yahoo.com. Then, at the top left corner of the screen, you’ll want to enter the ticker symbol you want to look up. In our example, it is RYVPX. When you’re done, hit the get quotes button!

Step 1

Step 2: Now, you should be looking at a Summary of the mutual fund RYVPX that looks something like this:

Step 2

Click on “Historical Prices” on the left side of the screen to proceed. In the image above, you can see it highlighted inside the red box.

Step 3: Now that we’re in the historical prices area, we need to decide what sort of data we want. Let’s say that we want to analyze RYVPX over the last 3 years and we want to be very, very specific. In the start date, we’ll enter Feb 16, 2004 and for the end date we’ll enter Feb 16, 2007 to give us the last 3 years of data, as of last Friday. Now, we’re not scared of lots of data, so let’s use daily returns. When you’re done, press “Get Prices”.

Step 3

Step 4: So we are now staring at the data we want. Now what? Let’s export it into Excel. This is surprisingly easy. Just scroll down to the bottom of the page in your browser and look for this:

Step 4

You should find it immediately under the Yahoo! data table and once you click it, you’ll be able to download and open the Excel File. Once the file is opened in Excel, you can move to step 5.

Step 5: You’ll be looking at the Data in Excel and in order to do your analysis, you’ll do various things with the data and manipulate it in all sorts of ways. That will all be covered in the upcoming posts, but for now, let’s calculate the % change in price from one day to the next (since running analysis on price is less common than running analysis on % change).

There should be data in columns A-G, as shown below. The highlighted column (H) is where you will want to input the calculations for percent change.

Step 5

Step 6: Finally, enter the formula into cell H2, as shown below. All you want to do is put the previous close in the denominator and the current day’s closing price in the numerator and then subtract 1 from that entire quantity. Drag that formula down and voila, you have the percent change from day to day! Note: The last day will not have a % change, this is ok. Make sure you delete the last % change, as it will show up at 1.00, you don’t want this!

Step 6

Now what?

Stay tuned over the next few weeks for various analyses you can do and metrics that you can calculate using this base data set. Remember that your analysis is not limited to RYVPX. Data for any ticker that you’re interested in (that Yahoo! has data for) can be downloaded and used to run different analyses.

You have the Yahoo! provided data and daily percentage changes in stock price. Feel free to explore and run any sort of analysis that you like and stay tune for some specific suggestions. Good luck!

Popularity: 71%