Excel 2002 Stock Quote Add-In: Limit only 200 symbols

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

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

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

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

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

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

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()

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()

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()

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

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

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?

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

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

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