Refreshable Stock Quotes in Excel or HTML

Posted by Linkin24 on April 07, 2010 (08:32PM)

I'm working on a personal project to help me manage my "Stocks Target List". Basically I have about 300 stocks in an excel spreadsheet that have the stock quote/ current price, Book Value, 52 Week High/Low, Dividend and Ex Dividend Date, Next Earnings Release Date, Earnings Per Share (EPS), Cash, Debt, Revenue, Forward P/E, and a Notes column. In addition there are two more columns. One I have a Buy Under Price which is the price I would like to buy the stock at or cheaper, and the other column is a preferably Price column which is a price that I think is an absolutely good deal for the stock (both values are made up by me). I've been trying to link the quote column to a msn.com or yahoo.com spreadsheet that gives me the quote info for each stock. 99% of the time it breaks the whole excel document or just doesn't update the proper fields.

I want to try and make this a web application instead but not sure if its possible. Is there a way I can make a spreadsheet or table, and have it update certain columns like the quote column from an external source such as google finance or yahoo finance? For example, is there a way to update it through google.com/finance with the particular stock symbol for each row.

Here's a small example of the format: I need the quote column updated automatically or with the click of a button to start with.
HTML Code:
Stock Buy under Preferably Quote BOOK 52 WEEK LOW/HIGH MXIM $19.00 $15.25 $19.44 $8.43 11.01 - 21.00 XLNX $25.00 $23.30 $26.04 $7.36 18.16 - 27.50 CY $11.00 $8.50 $11.47 $3.93 4.29 - 11.76 TSRA $18.55 $15.00 $20.32 $11.44 10.13 - 32.17 RFMD $3.85 $3.76 $5.02 $1.74 0.73 - 5.85

Posted by Linkin24 on April 07, 2010 (08:35PM)

Well I guess I can't format the example how I want too so if you need me to send the excel file to see what I mean I can.  I'm using the MSN refreshable stock quote tool on one excel sheet and refrencing it to the Live quote column for a particular stock.  However, the msn sheet will update the quote but it wont update on the information sheet it will just display the old quote when i first referenced it!

Posted by TheSnowMan on April 07, 2010 (08:41PM)

I am working on the same thing also.

Currently I have excel sheets to calculate profit/loss amoung different buy prices of stocks and I will expand it to different option strategies.

I also want to keep my watchlist of stocks in excel and setup alerts so I know when to buy or sell.

Posted by TheSnowMan on April 07, 2010 (08:54PM)

Look what I found here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=485FCCD8-9305-4535-B939-3BF0A740A9B1&displaylang=en

I installed it on Excel 2007, pretty nice thus far.  Allows you to add symbols and a variety of different fields and then has an update quote button.  Definitely going to be working with this tonight and see what all it does.

Posted by Linkin24 on April 07, 2010 (08:55PM)

I'm doing something similar.  For example if I get the quote column working - it will then compare the quote value to the Buy Under column for a particular stock and if the quote price is less than or equal to the buy under price, the quote price will be highlighted yellow (conditional formatting).  This will alert me that there is a good deal I should take advantage of.  I just can't get the quote column to actually be updated!

Posted by Linkin24 on April 07, 2010 (08:57PM)

I believe that's what I'm using!  However when the quote changes its price, it will update with the add in but not the cell your refferring to in your quote column (it will only show the value it initaly got even though its still referring or being referenced to the updated quote column).

Posted by TheSnowMan on April 07, 2010 (09:03PM)

hmm I will see if I can play with that.  I am loving this add-in though.

Posted by TheSnowMan on April 07, 2010 (09:13PM)

I wish I could test this but I have to wait for some change in the market...:-/

There is this add-in also:
http://www.traineetrader.com/importing-stock-quotes-to-excel-using-smf-add-in/

Don't really care for that one though

Posted by Linkin24 on April 07, 2010 (09:20PM)

I'll check that one out.  Here's another one that I used but having the same issue as its not refreshing in my data section only in the sheet that downloads the data.  Download yahoo3.xls it has a lot of different data you can pull including book value.

http://www.gummy-stuff.org/Yahoo-data.htm

This one is more sophisiticated and actually broke my excel sheet once so make sure to make a backup.  If I could get this one working properly it would be amazing.

Posted by Linkin24 on April 07, 2010 (09:32PM)

You can also do a query to select certain information to pull, but again mine doesn't update my information sheet once a number changes.  To do this using excel 2007:

1.  Click Data tab

2.  Click From Web icon under Get external Data Ribbon.

3.  In Address type in www.yahoo.com/finance

4.  Type your quote in the box where there’s a get quote button and then hit the button.

5.  Select the arrow next to Last Trade as it will select that table and will highlight that section.

6.  you can also go to the Key Statistics page and pull the tables for Revenue, Forward P/E, Debt, Cash, Book Value, Etc.

7.  Click properties to change any settings such as auto refresh

8.  click Import.

The problem with this is that it will select all information in the table query.  For example if you just want quote it will give you the trade time, change, prev close, open, bid, ask, 1 year target est.  I can't see how to eliminate certain information.  The other problem is that you have to do this process for each ticker which can take days (I have 300+ stocks in my sheet).  The other problem again is that it doesn't Refresh if you reference it, only refreshes the values in the query page! 

I'm downloading Office 2010 Beta to see if it fixes this refresh issue.  Tomorrow, see if its just me or if yours isn't refreshing either once the price changes.

Posted by The Otter Way on April 07, 2010 (09:36PM)

You can use excell and DTNIQFEED DDE interface to pull live data Level 1 Data using DDE...  Here is a link... You can monitor up to 500 Symbols...

http://www.iqfeed.net/index.cfm?displayaction=data§ion=main

This is compress data streams exploding into your application.  There also is a TA Lib that will add 400 different indicators, Oscillators, and etc...

Good Luck... I'm using C#/Delphi/C++... Same, but OOOOP's...  You also can automate ordering with your excell but not with Trade King...

Posted by TheSnowMan on April 07, 2010 (09:36PM)

Why do you need to refresh, can you not just reget the information by just using the formula for that cell.  Then, I would guess, if you clicked update it would update all areas that use the formulas?

Posted by Linkin24 on April 07, 2010 (09:47PM)

I'll check that out in a momemt The Otter Way, Thanks.

TheSnowMan, I need to refresh because obviosly the quote changes daily.  But you're right if i click the cell that has the formula that references the sheet that IS updating and then hit enter it will update correctly.  However, I have over 300 stocks I'm watching and it would take over an hour to click each one and then hit enter to get the most updated price.  You cant't just click the column and hold down enter as you actually need to click the cell, then the formula box, then enter for it to recognize the update.  I just don't get why the value changes in one sheet but not the other that refrencing it!

Posted by TheSnowMan on April 07, 2010 (10:04PM)

What I am saying is why do you reference when you can just put in the formula to get value you need.

For example:

Sheet 1:

You get a stock, ("MSFT") and its Last Price. Cells A1 and A2, respectively

Sheet 2:

You need the current value (A2) from Sheet 1.

You have: =Sheet1!A2

and its not updating

What I am suggesting is turning that formula to this one:

=MSNStockQuote("MSFT","Last Price","US")

That way when you click update, it will update all the values.
(I don't know if it will actually update all the values or you will have to go to every sheet and click update.)

You might even be able to reference the stock symbol so something like:

=MSNStockQuote(Sheet1!A1, "Last Price", "US")

Posted by Linkin24 on April 07, 2010 (10:15PM)

You're right how I'm refrencing it. but I just tried your method and Hmmmm I'm getting N/A for the value.  Can you send me an example file to my e-mail:  Linkin2424@Hotmail.com.  Thanks!

Posted by The Otter Way on April 07, 2010 (10:26PM)

Download TA Lib... It has working examples of live data with a free trial...


Here is the link and it will load the Excell Library that also will include Oscillators etc...


http://ta-lib.org/hdr_dw.html

Posted by TheSnowMan on April 07, 2010 (10:30PM)

Example sent.

Posted by Carlton Banks on April 08, 2010 (08:28AM)

Nice thread.  I'm sure the guys from the 4X2 trading strategy thread would be interested in seeing this.

I'm playing with the excel add-in now.

Posted by ssapp80 on April 08, 2010 (10:16AM)

Linkin24, I use the MSNstockquote addin as well.  This is how I use it.....maybe this will help.

Reserve a column for your symbol, then reference it like so.....lets say the symbol is in column B row 2. (I started @ row 2 because I usually use row 1 for headers)

In column C if you wanted the last price you would do =MSNStockQuote($B2,"Last","US")  If you reference B2 like $B2 with the $ in front of the cell you can easily continue to add symbols in column b and copy/paste the formula on down the spreadsheet and it will reference whatever symbol relative to the row its pasted in vs using !B2 which is an absolute reference. 

Hope this helps.

One thing to note.......I've had a problem with this add-in once you have a high number of symbols you are tracking.  You might start seeing #VALUE or something like that.  What I've done as a work around is once I have about 75 symbols in a sheet I'll just start adding them to another sheet.

I also sent you my template as well.

Posted by golax on April 08, 2010 (10:43AM)

All,

You might want to check out the Yahoo Group called: "smf_addin · EXCEL Stock Market Functions Add-in" The owner, Randy Harmelink, is an EXPERT developer with Excel and in using this tool. He is an active investor and is very responsive to polite calls for help.


The group description is listed below.

This group is the home base for the Stock Market Functions add-in developed by Randy Harmelink. This add-in contains a number of user defined functions for EXCEL that can allow data to be extracted and/or retrieved from the web and placed directly into EXCEL cells or ranges.

The "Files" area of this group contains the add-in, documentation on its functions, and a number of templates that have examples of how the various functions can be used. The "Links" area of the group has a number of tips and FAQs.

The add-in was developed using EXCEL 2002 under a WinXP operating system, but my current configuration is EXCEL 2007 under Vista, so other platforms and/or versions may have issues.

If you're reporting a problem, remember that I'm not a mind reader -- "It doesn't work right" is not a helpful description of a problem. Please try to include relevant information, such as:

-- The specific function(s) or template(s) you are having problems with.
-- The parameter(s) you are using with the function(s).
-- The version of EXCEL you use.
-- The operating system you're running EXCEL under.
-- The steps you took to try to resolve the problem.
-- The ticker symbols or URLs that are being used.
-- The results you expect to get and what you actually get.

Good luck!

You must Log In to post to this forum.

Not a member? Register Now to …

  • See what other traders are doing
  • Make your own trades public
  • Share your thoughts on a trade
  • Join or start a group
  • Connect with like-minded traders