Excel 2002 Stock Quote Add-In: Limit only 200 symbols
Posted by Tan Le on September 18, 2001 8:51 AM
Hi Juan, Eric, Craig, Richard:
Thank you for your help for every time I post a question. You guys are the best Excel gurus on the face of this planet.
Here is another of my question: Excel 2002 has this free add-in: http://office.microsoft.com/downloads/2002/Msnsq.aspx . I like it a lot. But it limits to 200+ stock symbols per use. Since there are 10,000+ US/Cananda stocks for Nasd, NYSE, AMEX, and OTBCC on my worksheet, I can't see more than 200+ stocks at same time. This is an Excel feature that you just type the stock and it shows the price (high, or low, volume). It is a 15-delay and requires a manual refresh. You guys might want to download it and try it for an experiment. In details, I have 10,000+ rows of stock symbol in Col A with row 1 titled as Symbols. Then Column B has the title Last Price with also 10,000+ equivalent number of rows to display the last price of every stock. Here how it looks:
Column A Column B
Symbols Last Price
In both cells B2 and B3, they have this function built by the add-in: =MSNStockQuote($A2,"Last Price","US") and =MSNStockQuote($A3,"Last Price","US").
This function shows all last price up to 200+ then any cell higher than that, say row 250, start showing the "#VALUE!" text. If I delete row 2 to 249 in column B and hit refresh button (on the menu bar) then the #VAlUE! start to disappear and replaced by the last price data. But again, up to 200+ per worksheet. I tried by opening another worksheet but it recognizes it somehow and limits 200+ in combined active worksheets. I hope you uinderstand so far. Anyway, I think it sees for the number of =MSNStockQuote($CellLocation,"Last Price","US") and count each as one. So what I am trying to do is collecting data once a day at 4 PM Eastern time when the market is closed. So the VBA or equation in Excel 2002 should do the trick to eliminate the =MSNStockQuote($CellLocation,"Last Price","US") as soon as it grabs the Last Price data and convert into a real value. Does that for 10,000 rows by Filling Down. Or creating column C to transfer the last price from Col. B and delete the =MSNStockQuote($B2,"Last Price","US"). I hope this makes sense.