Importing Stock Quotes to Excel using SMF Add-in

In our previous article we used a simple method for importing stock quote data into Excel. Today we will look at a robust automated method for querying more data sources then just Yahoo finance. The Stock Market Functions Add-in for Excel is a free tool developed by by Randy Harmelink. The stock market functions Add-In allows you to import data from sites like Yahoo, Business Week, Reuters, Google, Bloomberg, MSN and many more. In the latest release there are beta features for several technical analysis functions like

• Accumulation Distribution Line
• Average True Range
• Commodity Channel Index
• Simple Moving Average
• Moving Average Convergence Divergence
• Exponential Moving Average

• On Balance Volume
• Rate of Change
• Relative Strength Indicator
• Stochastic’s

In order to download the Excel SMF Add-in you will need to sign up to the Yahoo finance group. After signing up you will be able to download the Add-in, templates and documentation. The documentation for free software programs is usually somewhat lacking however Randy and the community have done a phenomenal job documenting the functions of this Add-in.

Installing SMF under Vista and Excel 2007

As the documentation does not go into detail about the install under Excel 2007 I will quickly go over the process.

Step One

Download RCH_Stock_Market_Functions.zip from the files section of the Yahoo finance group.

Step Two

Create a new folder called “SMF Add-In” in the directory “C:\Program Files\”.

Note: You will need to have admin access to carry out this operation.

Step Three

Extract the file to the folder you created as highlighted below.

Note: You will need to have admin access to carry out this operation.

Step Four

You will now need to enable the Add-In to do this you will need to open the Excel options dialog window as shown below:

In the Excel options dialog select Add-Ins and then Manage Add-Ins.

Another dialog window will open with several Add-Ins displayed click brows and locate the RCH_Stock_Market_Functions Add-in.

The final step is to test that the Add-In has been initialised properly. In a new workbook highlight 20 rows and 7 columns. With the area highlighted in the formula bar type the following:

=RCHGetYahooHistory(“MSFT”)

Do Not just press enter instead press CTRL + SHIFT + ENTER and the Microsoft data should be added. This process is highlighted below.

The Microsoft data should now automatically be placed in the cells this is shown below.

Obviously the cells require formatting but the power of this Add-In is immense. In upcoming articles I will investigate some more features, until then make sure you have a read through the documentation.

About the author

Mark - Is a 28 year old form Australia. He has a Bachelor in Economics and a Major in CS. He is currently working towards his CFP. His interests include algorithmic trading systems, artificial intelligence, game theory and poker.