MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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
CSCO 16.50
DELL 19.90

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.

Best regards,

Tan Le


Posted by Juan Pablo on September 18, 2001 10:37 AM

Hi, i think i know what you're trying to do.

Put the formula, recalculate, then copy and paste as values, right ?

What is making me think is how do you know when the value has been obtained...

Juan Pablo

-------------------

Posted by Tan Le on September 18, 2001 10:53 AM

Hi Juan,

Man, you are quick! Thank you for generalizing my problem in a few words! Yes, basically I want to fool MSNStockQuotes Engine that there is none of the function =MSNStockQuote($A3,"Last Price","US")existing in the worksheet so it keeps filling data all the way using VBA or Filling Down. That's my guess of how it knows to limit up to 200 data. As far as knowing it gets the right data, I guess I can verify against the data from the day ago based on the increase or decrease or change. For ex: Dell was closed yesterday at 20. Today it closed at 19. So the decrease (change) is -1. So I can check by today-yesteday=change. It this case: 19-20=-1. If true by +/- 0.01 cent then it's ok.

What do you think, Juan?

Tan Le

Posted by Juan Pablo on September 18, 2001 12:15 PM

Look, i've been thinking about it and i guess you could do a macro that create the formula and then copy the value.... but my "fear" is that it would copy it as values too soon.

So, that got me to another possible solution, do 200 rows, wait a little, then do another 200 and so on... and this takes me to another question... does it take too long to update one (Or 200 stocks ?)... i mean, i don't have Excel 2002, i'm using 97, so i just have to imagine what you're doing...

Juan Pablo

Posted by Tan Le on September 18, 2001 1:21 PM

I tested the MSNStockQuote again and found its weakness. I need to restate about limit of 200. It does not look for that function, rather it looks for the symbols. If it has to send out 200 different symbols, then it maxsimizes its regulation. I think MS does this so it does not clog the bandwidth if there are thousands of people update 10,000 every 15 minutes. That would be bad. So I tried this: I typed three symbols: AMAT, DELL, INTC and fill down to 6000 rows. Amazing, all prices are displayed,. No more #VALUE! So I concluded that it looks for the different symbols up to 200. It does not care about the row quantity. So I think the best way is to fool the Engine that I have only a few symbols after the cut and paste. To answer your question, it updates very quickly (1 sec per symbol). I think I have to use VBA:


Column A Column B
Symbols Last Price
CSCO 16.50
DELL 19.90


Given that Column A is always there and always is constant because it's just stock symbol. Column B will change but the goal is to copy/paste/overwrite using Column C as a borrowed space. At all times, there is only one MSNStockQuote in the do loop until the very last row. In the end, there is no function in the worksheet except all values.

So the VBA will run like this based on the cell locatipon above:

1. Go go B2
2. Paste the =MSNStockQuote($A2,"Last Price","US")
3. Copy B2
4. Go to C2
5. Paste Special Value to C2
6. Copy C2 over B2.
7. Select Yes to overwrite B2
8. Go to B3
9. Repeat step 2 but replace row 2 to row 3
10. Do loop until column A cell equals to "" (empty space).

I am stuck on the step 9 and 10. Please help. Thanks Juan and those who can assist.

TAn Le

Posted by Juan Pablo on September 18, 2001 1:46 PM

Well, you almost got it, but i think you're doing an extra step (B2 to C2, then C2 to B2 again...)

Try this one. Insted of copying and pasteing each cell i'm doing that every 195 cells (Not 200 just in case)...

Sub UpdateStock()
Dim i, f, j As Integer
Application.ScreenUpdating = False
f = Range("A65536").End(xlUp).Row
j = 1
For i = 1 To f
If i Mod 195 = 0 Then
Range(Cells(j, 2), Cells(i, 2)).FormulaR1C1 = "=MSNStockQuote(R[1]C1,""Last Price"",""US"")"
Range(Cells(j, 2), Cells(i, 2)).Copy
Range(Cells(j, 2), Cells(i, 2)).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
j = i + 1
End If
Next i
Range(Cells(j, 2), Cells(i - 1, 2)).FormulaR1C1 = "=MSNStockQuote(R[1]C1,""Last Price"",""US"")"
Range(Cells(j, 2), Cells(i - 1, 2)).Copy
Range(Cells(j, 2), Cells(i - 1, 2)).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Tell me if it works...

Juan Pablo

------------------

Next i
End Sub


Posted by Tan Le on September 18, 2001 2:04 PM

I'll definitely give your advise a try. I will answer you very soon.

Thanks Juan,

Tan Le Well, you almost got it, but i think you're doing an extra step (B2 to C2, then C2 to B2 again...) Try this one. Insted of copying and pasteing each cell i'm doing that every 195 cells (Not 200 just in case)... Sub UpdateStock()

Posted by Tan Le on September 18, 2001 3:45 PM

Hi Juan,

That works so well! You're awesome! Two things: Since column is blank (without a title at row 1), after the VBA is executed, it shows all prices in col. B except that it does not match with col A. The small problem is that col B is one row being shifted UP during the VBA run. I insert a new cell at B1 to shift the entire col. B down by 1 row so that col A and B match symbol and price, respectively. How do I fix that, Juan? Another is you gave a Mod of 195 to 0. It did stop at 197. Everything below is displayed with #N/A. How do you add to the loop to continue all the way down to the last row where the empty space begins?

Best regards,

Tan Le

Well, you almost got it, but i think you're
doing an extra step (B2 to C2, then C2 to B2 again...) Try this one. Insted of copying and pasteing each cell i'm doing that every 195 cells (Not 200 just in case)... Sub UpdateStock()

Posted by Tan Le on September 19, 2001 4:07 AM

Juan,

Something funny happened. The original VBA does not fill the data when I do this:

1. Have column A filled in with all symbols
2. Run VBA you provided.
3. Column B is filled with #NA

But it shows data when I do this:

4. Write a formula =MSNStockquote(A2, "Last Price","US") in the first A2 cell.
5. Fill down to A102.
6. Correct data appears instantly.
7. Then I delete the data in column B
8. Run the VBA that you provided
9. Correct data appears instantly.
10. Minor format correction: insert a blank cell right above the first data in col. B in order to shift the entire set of data downward by 1 cell. This allows to correspond with col. A symbol.

Another strange thing:
11. Delete the data content in col B.
12. Run VBA
13. Data appears instantly

or

14. Delete the data content in col. B
15. Save the worksheet
16. Run VBA
17. Data appears instantly

but,

18. Delete the data content in col. B
19. Save the worksheet
20. Close down all active Excel and VBA editor
21. Re-open Excel
22. Open the same saved workbook with empty col. B
22. Run VBA
23. Only #NA appears (back to step 3 above)

I think VBA stores these pasted data in the background and when Excel and VBA are closed, the data are lost. What I am going to do is manually and painfully fill all cells with yesterday market data and save the workbook. Then tomorrow during the opening market day, I'll run a test again to see if it loads the current data. I think it will. Do you know anyone with Excel 2002 so you can troubleshoot the script or maybe there is something wrong with MSNstockquote. Or maybe, you're right about the speed of the copy/paste. How do you slow down or delay the script by 1 second in between every step?

Best regards,

Tan Le
(maybe I should have this problem converted into a MrExcel consultant project. Are you part of that team?)

Well, you almost got it, but i think you're doing an extra step (B2 to C2, then C2 to B2 again...) Try this one. Insted of copying and pasteing each cell i'm doing that every 195 cells (Not 200 just in case)... Sub UpdateStock()

Posted by Tan Le on September 19, 2001 5:08 AM

Hi Juan,

I pulled the Internet wire connection from my computer and ran the VBA script that you provided. Guess what? It pastes the same as it was connected to the Internet. I am very sure that VBA stores these data in the buffer. I can't see that in the script. Do you?

Tan Le

Posted by Juan Pablo on September 19, 2001 6:24 AM

Ok, read all of what happened... here are my thoughts (Again, thinking what could be wrong...)

1. First time you run the macro the function is not loaded in memory, that's why if you put manually the function and then run the macro it works.

2. Second, i'll correct it to give a 1 second delay between each group, and that it starts from row 2. This is not hard, just do the For..loop thing from 2 to F, that's it.

Here's the macro

Sub UpdateStock()
Dim i, f, j As Integer
Application.ScreenUpdating = False
f = Range("A65536").End(xlUp).Row

Cells(2,2).FormulaR1C1= "=MSNStockQuote(R[1]C1,""Last Price"",""US"")"

j = 2
For i = 3 To f
If i Mod 195 = 0 Then
Range(Cells(j, 2), Cells(i, 2)).FormulaR1C1 = "=MSNStockQuote(R[1]C1,""Last Price"",""US"")"
Application.Wait Now + TimeValue("00:00:01")
Range(Cells(j, 2), Cells(i, 2)).Copy
Range(Cells(j, 2), Cells(i, 2)).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
j = i + 1
End If
Next i
Range(Cells(j, 2), Cells(i - 1, 2)).FormulaR1C1 = "=MSNStockQuote(R[1]C1,""Last Price"",""US"")"
Application.Wait Now + TimeValue("00:00:01")
Range(Cells(j, 2), Cells(i - 1, 2)).Copy
Range(Cells(j, 2), Cells(i - 1, 2)).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

I hope it doesn't slow too much the macro, but it will (1 second for each 195 rows and you have 10.000, that's a lot of seconds...)

Anyway, hope that works.

Juan Pablo

Posted by Juan Pablo on September 19, 2001 6:25 AM

That's strange... but it's not the script, i think is Excel... I pulled the Internet wire connection from my computer and ran the VBA script that you provided. Guess what? It pastes the same as it was connected to the Internet. I am very sure that VBA stores these data in the buffer. I can't see that in the script. Do you?

Posted by Tan Le on September 19, 2001 8:27 AM

Juan,

Based on what you said about having to load the function into memory, does this mean that I have to fill all cells with the function msnstockquote?

I changed MSNStockQuote(R[1]C1,""Last Price"",""US"") to R[0]C1 and that fixes the shifting in col. B. I kept j=1. Your script works very well!!! Thanks again Juan!

Tan Le Ok, read all of what happened... here are my thoughts (Again, thinking what could be wrong...) 1. First time you run the macro the function is not loaded in memory, that's why if you put manually the function and then run the macro it works. 2. Second, i'll correct it to give a 1 second delay between each group, and that it starts from row 2. This is not hard, just do the For..loop thing from 2 to F, that's it. Here's the macro Sub UpdateStock() Cells(2,2).FormulaR1C1= "=MSNStockQuote(R[1]C1,""Last Price"",""US"")" j = 2

Posted by Tan Le on September 19, 2001 11:50 AM

Juan,

Based on what you said about having to load the function into memory, does this mean that I have to fill all cells with the function msnstockquote?

I changed MSNStockQuote(R[1]C1,""Last Price"",""US"") to R[0]C1 and that fixes the shifting in col. B. I kept j=1. Your script works very well!!! Thanks again Juan!

Tan Le Ok, read all of what happened... here are my thoughts (Again, thinking what could be wrong...) 1. First time you run the macro the function is not loaded in memory, that's why if you put manually the function and then run the macro it works. 2. Second, i'll correct it to give a 1 second delay between each group, and that it starts from row 2. This is not hard, just do the For..loop thing from 2 to F, that's it. Here's the macro Sub UpdateStock() Cells(2,2).FormulaR1C1= "=MSNStockQuote(R[1]C1,""Last Price"",""US"")" j = 2

Posted by Juan Pablo on September 20, 2001 6:47 AM

Try this one

Sub UpdateStock()
Dim i As Long, j as Long, f as Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
f = Range("A65536").End(xlUp).Row 'Finds last Stock symbol
Range(Cells(2, 2), Cells(f, 2)).Clear 'Clear column B.
Range(Cells(2, 2), Cells(f, 2)).FormulaR1C1 = "=MSNStockQuote(RC1,""Last Price"",""US"")" 'Puts the formula in Column B

j = 2
For i = 2 To f
If i Mod 198 = 0 Then 'Copy groups of 195 cells
Cells(1, 3).FormulaArray = "=OR(ISNA(R" & j & "C[-1]:R" & i & "C[-1]))"
Cells(1, 4).FormulaArray = "=AND(ISNUMBER(R" & j & "C[-1]:R" & i & "C[-1]))" '<=================
While Cells(1, 3) Or Not Cells(1, 4)
Range(Cells(j, 2), Cells(i, 2)).Calculate
Cells(1, 3).Calculate
Cells(1, 4).Calculate
Wend
Range(Cells(j, 2), Cells(i, 2)).Copy
Range(Cells(j, 2), Cells(i, 2)).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
j = i + 1
End If
Next i
Cells(1, 3).FormulaArray = "=OR(ISNA(R" & j & "C[-1]:R" & i & "C[-1]))"
Cells(1, 4).FormulaArray = "=AND(ISNUMBER(R" & j & "C[-1]:R" & i & "C[-1]))" '<=================
While Cells(1, 3) Or Not Cells(1, 4)
Range(Cells(j, 2), Cells(i, 2)).Calculate
Cells(1, 3).Calculate
Cells(1, 4).Calculate
Wend
Range(Cells(j, 2), Cells(i, 2)).Copy
Range(Cells(j, 2), Cells(i, 2)).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Cells(1, 3) = ""
Cells(1, 4) = ""
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub