On-Line Course for Reading Stock Prices

Basic Example with P/E Ratio Comparison of Yahoo, Google and Market Watch

Let’s start with a simple case where you want to make a comparison of financial statistics for a bunch of companies. (You should understand what drives things like the P/E ratio and the EV/EBITDA ratio before you get into comparative analysis).

  • The first step is to go to yahoo.finance.com or google.com or marketwatch.com and find the URL.This is illustrated in the picture below. All you do is copy the URL and paste it into your excel sheet.
Googlefinance URL.JPG
  • The second step is to separate the URL so you can read other stocks. You need to find the URL that works and then make a new URL. The INDEX function is perfect for this as you can make a list of stocks and then pick one.
Index to Read Stocks.JPG
  • The third step is to separate the URL into components that do not have the stock and then to re-combine it with a simple & sign. The stock symbol can come from the INDEX function and the URL should have a range name.

 

re-combine.JPG
  • The fourth step is to create a macro with workbooks.open as shown below. When you operate this macro it puts the data in another file.
workbooks.open.JPG
  • The fifth step is to create a macro that copies and pastes special as values to back to the original sheet. You can also re-name the sheet. Then you should put stuff in the macro that makes the process generic as shown below. Make sure you do not close the file because you will have to adjust the macro as shown below. (Don’t forget the rule – when you make a macro, save the workbook before running it.) You can find this macro in the spreadsheet below.

 

Adjusted VBA.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

VBA in read before adjustment.JPG

 

 

 

  • The sixth step is and easy step which is to read all of the stocks into separate sheets rather than make one sheet. This is simple because of the index function. All you do is make a little loop that goes around for the same number as items in the index function. Then you make a FOR NEXT loop where you assign the code number used in the INDEX function to the counter in the loop. Then you call the program that reads the individual stock. This is illustrated in the VBA code below.
  • The final step is using INDIRECT, MATCH and INDEX to find the data in the various sheets. To do this you need the sheet names so you can look for all of the data. Then you have to put the sheet names together which can be a little painful with “’” when there is a space in a sheet name. The excerpts below illustrate how to use the MATCH and then the INDEX and then show the summary report.

 

Match Part 1.JPG

 

Index and Indirect.JPG

 

The final output is illustrated below — node the difference in the P/E ratios from the different sources. The excel file associated with all of this is included below the picture. This file has all of the VBA code.

Final Summary.JPG