Moving data from memory to a cell VERY slow 2

[FoxProProgrammer]

I wrote a macro in VBA for Excel that downloads stock quotes from Yahoo's website and updates a portfolio in a spreadsheet. Yahoo returns the data for each stock ticker as a comma delimted string. For example, if I ask for a quote on Microsoft, the website returns the following string:

MSFT,55.76,"5/16/2002","4:01pm",+0.68,54.71,56.04,54.65,26703600

Where each field is defined as:
Field 1: Ticker symbol
Field 2: Closing price
Field 3: Date of last trade
Field 4: Time of last trade
Field 5: Change for today
Field 6: Open
Field 7: High
Field 8: Low
Field 9: Volume

My program builds a string of multiple stock tickers that it transmits to Yahoo. For example, if I want quotes on five stocks, I build a string and Yahoo returns:

Quote1...9 fields
Quote2...9 fields
Quote3...9 fields
Quote4...9 fields
Quote5...9 fields

where each quote is the same format that I included above.

The web query works fine, and the data is stored in a sheet temporarily while I parse it. I only need the ticker symbol, close, and change, but I can't figure out a way to exclude that from the query to Yahoo's website. Anyway, that isn't the problem; it just creates more data that I have to go through.

I then parse each string and store each field in a cell in another sheet. The parse routine looks for a comma, and uses the Instr and Mid functions to parse each field. Here is the code:

Private Sub Parser()

' Set active sheet to Update
Sheets("Update&quot.Select

Dim i, j, start, position As Integer
Dim celldata, cellsource, celldest, getstr As String

celldata = ""
cellsource = "A1"
celldest = ""
getstr = Range(cellsource).Value
j = 1

' Parse rows and place symbol, last, date, time, and change into cells
Do While getstr <> &quot;&quot;

start = 1
position = 0

' Parse the five fields from each string.
' i = 1 (Symbol)
' i = 2 (Last)
' i = 3 (Date)
' i = 4 (Time)
' i = 5 (Change)

For i = 1 To 5

' Get the position of the next comma
position = InStr(position + 1, getstr, &quot;,&quot

' Parse the data between the last comma and next comma
celldata = Mid(Range(cellsource).Value, start, position - start)

' Store the data in the spreadsheet
Select Case i
Case 1
celldest = &quot;B&quot; & j
Case 2
celldest = &quot;C&quot; & j
Case 3
celldest = &quot;D&quot; & j
Case 4
celldest = &quot;E&quot; & j
Case 5
celldest = &quot;F&quot; & j
End Select
Range(celldest).Value = celldata

' Increment the starting position to look for the next comma
start = position + 1
Next i

j = j + 1
cellsource = &quot;A&quot; & Trim(Str(j))
getstr = Range(cellsource).Value

Loop

End Sub

The parse routine works fine and is very fast. However, the statement that actually writes the parsed string to the cell is EXTREMELY slow. When I run the program on 20 ticker symbols, I can literally count seconds as it places each string in the cell for each ticker symbol. For example, it writes symbol 1, a second or two go by and it writes the last price for symbol 1, a few more seconds go by and it writes the date of the last trade, etc. It takes a minute or two to finish 20 symbols. I don't understand what is going on here to cause such a long delay. I ran in debug mode and determined that the statement that is bogging down the system is:

Range(celldest).Value = celldata

This is the statement that writes the data to the cell.

Does anyone know why this is so slow, or if there's another way to accomplish the same thing that might be faster?

I'm sorry for such a long post, and hope it didn't scare everyone away. The gist of the problem isn't that complicated, but I wanted to provide as much information as might be needed.

Thanks a lot!

dz

 

[rmikesmith]

dz,

I've made some changes to your code to streamline it somewhat (listing below - changes in red). However, when I ran your original procedure on 20 ticker strings it parsed them in less than 1 second. So, I don't think it is your routine that is causing the problem. While writing to worksheet cells is much slower than reading them, it is still very fast. I wouldn't expect to see such a slow speed unless there were hundreds or thousands of such operations. One note for future reference regarding declaring variables: If you use a Dim statement such as
Dim i, j, start, position As Integer
then only the variable &quot;position&quot; is declared as an integer. The others on the line are declared as Variants just as if you'd typed Dim i

HTH
Mike


Private Sub Parser()

Dim i As Integer, j As Integer, start As Integer, position As Integer
Dim celldata As String, cellsource As String, celldest As String, getstr As String

Application.ScreenUpdating = True

celldata = &quot;&quot;
'cellsource = &quot;A1&quot;
'celldest = &quot;&quot;
'getstr = Range(cellsource).Value
getstr = Cells(1, 1).Value
j = 1

' Parse rows and place symbol, last, date, time, and change into cells
Do While getstr <> &quot;&quot;

start = 1
position = 0

' Parse the five fields from each string.
' i = 1 (Symbol)
' i = 2 (Last)
' i = 3 (Date)
' i = 4 (Time)
' i = 5 (Change)

For i = 1 To 5

' Get the position of the next comma
position = InStr(position + 1, getstr, &quot;,&quot

' Parse the data between the last comma and next comma
' celldata = Mid(Range(cellsource).Value, start, position - start)
celldata = Mid(getstr, start, position - start)
' Store the data in the spreadsheet
' Select Case i
' Case 1
' celldest = &quot;B&quot; & j
' Case 2
' celldest = &quot;C&quot; & j
' Case 3
' celldest = &quot;D&quot; & j
' Case 4
' celldest = &quot;E&quot; & j
' Case 5
' celldest = &quot;F&quot; & j
' End Select
' Range(celldest).Value = celldata
Cells(j, i + 1).Value = celldata
' Increment the starting position to look for the next comma
start = position + 1
Next i

j = j + 1
' cellsource = &quot;A&quot; & Trim(Str(j))
' getstr = Range(cellsource).Value
getstr = Cells(j, 1).Value
Loop
Application.ScreenUpdating = True

End Sub

 

[FoxProProgrammer]

Hey, Mike:

Thanks for the code suggestions. The program is smaller the way you did it. Thanks also for the information about declaring each variable separately. I thought it worked the same way as the old Basic, but you are right, it defaults to a variant.

I ran the code that you suggested, and there's no speed improvement. I ran the program in debug mode. First, I set a breakpoint at the statements below:

Cells(j, i + 1).Value = celldata
start = position + 1

I used a stopwatch to measure the amount of time that it took to execute the Cells(j,...) statement. It took between 3 and 4 seconds to process it each time.

Next, I set a breakpoint at the statement j = j + 1, and measured the amount of time that it took to cycle through the entire loop. It took between 13 and 15 seconds for each cycle, which is in the ballpark of 3 to 4 seconds for each field since there are five of them.

There's one thing that I forgot to post, but it doesn't affect the speed. The spreadsheet has fomulas that point to the data in the updated cells so it can update the portfolio. I use the following statement to update the portfolio after the web query and parser are done.

=VLOOKUP(A10,Update!B:F,2,FALSE)

One of these formulas is in each cell that needs to be updated. I thought that maybe the lookup was slowing it down, so I deleted all the lookup formulas and reran the program. It didn't change the speed at all. I sent my program to a friend about two weeks ago so he could run it on his computer. He told me that it ran slow as molasses there too. We both have relatively fast computers with a decent amount of memory (Pentium 4 with 512 MB RAM). If this ran fast on your computer, then something else must be wrong in my spreadsheet that is slowing it down. The common link between me and my friend's computer is that we both ran the same spreadsheet. I sincerely appreciate the time that you took to run my program and suggest the code changes. Your program is more streamlined, but unfortunately it didn't solve the issue. By the way, I think you meant to type Application.ScreenUpdating = False in the first instance of this statement? You had = True.

Thanks again,

dz

 

[FoxProProgrammer]

By the way, I updated 27 symbols tonight and it took about 6.5 minutes to parse. The web query only took a few seconds. Since when is a query on the web faster than parsing a few strings on a fast computer? lol

 

[StewartJ]

Why is screen updating true? If you hide the app (visible = false), it saves the system having to refresh the screen and that will affect speed.

Is the spreadsheet automatically recalculating for each entry (whether or not directly referenced?). If so, switch it off.

 

[rmikesmith]

dz,

Yes, I intended ScreenUpdating to be true!

Just thought of something... The apparently slow writing of a value to a cell indicates something else is happening in conjunction with this operation. Go into Tools/Options and select the Calculation tab. Check to see if Recalculation is set to automatic. If it is, change to manual and re-run your procedure. If this corrects the problem you can add the following statements to your procedure:

'At beginning of Sub

Application.Calculation = xlCalculationManual

'At end of Sub

Application.Calculate

'Force a recalc

Application.Calculation = xlCalculationAutomatic

Regards,
Mike

 

[FoxProProgrammer]

Thank you Mike and Stewart! Turning off the automatic recalc at the beginning of the sub and turning it back on after the parser finishes took care of the problem. Many thanks for all your help, especially Mike for the code modifications.

dz

 

[rmikesmith]

StewartJ,

Let me try this one more time; I must be having a brain cramp.
I meant to set ScreenUpdating to FALSE!!!

Thanks for calling me on this.


Regards,
Mike