5
\$\begingroup\$

The program downloads stock information from Yahoo! Finance and displays it in the spreadsheet. On my Mac the program takes 10 minutes to get data for approximately 4000 stocks and on the PC it takes 45 minutes. I wrote another version which copies and pastes the data instead of iterating through each price value, but every time I run that macro, the runtime increases. That version takes about 15 minutes on a PC and 45 minutes on a Mac.

I don't know what is going on but I just want a program that runs in < 15 minutes on both operating systems and has a consistent runtime.

Option Explicit

Sub GetData()

Application.Calculation = xlCalculationManual

Dim numb_tickers As Integer
numb_tickers =        Application.WorksheetFunction.CountBlank(Worksheets("Sheet1").Range(Sheet1.Cells(2, 2), Sheet1.Cells(5000, 2)))
numb_tickers = 5000 - numb_tickers

Dim start_ticker As Integer
start_ticker = Sheet3.Cells(5, 2) + 1
Dim end_ticker As Integer
end_ticker = Sheet3.Cells(6, 2) + 1

Dim x As Integer
For x = start_ticker To end_ticker

    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
    Dim LastRow As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("Data").Cells.Clear

    Set DataSheet = ActiveSheet

        StartDate = Sheet3.Cells(2, 2)
        EndDate = Sheet3.Cells(3, 2)
        Symbol = Sheet1.Cells(x, 2)
        Sheets("Data").Range("a1").CurrentRegion.ClearContents

        qurl = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol
        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Sheets("Data").Range("a1") & "&q=q&y=0&z=" & _
            Symbol & "&x=.csv"

QueryQuote:
             With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                On Error GoTo ErrorHandler
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

            Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False

         Sheets("Data").Columns("A:G").ColumnWidth = 12


Dim numb_days As Integer
numb_days = Application.WorksheetFunction.CountBlank(Worksheets("Data").Range(Sheet2.Cells(2, 7), Sheet2.Cells(1000, 7)))
numb_days = 1000 - numb_days

Dim z As Integer
For z = 2 To numb_days + 1

    Worksheets("Sheet1").Cells(x, z + 5) = Worksheets("Data").Cells(z, 7)

Next z

ErrorHandler:  Resume Next

Next x

Dim xyz As Integer
For xyz = 2 To numb_days + 1
    Worksheets("Sheet1").Cells(1, xyz + 5) = Worksheets("Data").Cells(xyz, 1)
Next xyz

Application.Calculation = xlCalculationAutomatic

End Sub
\$\endgroup\$
7
  • 2
    \$\begingroup\$ Is it possible the mac just has better specs? \$\endgroup\$ Jul 29, 2015 at 19:09
  • \$\begingroup\$ I'm with John, check the specs. My macbook has a quad core processor and three times the memory of my Windows desktop. \$\endgroup\$
    – MatthewD
    Jul 29, 2015 at 19:15
  • \$\begingroup\$ Specs are not the issue. The PC I am using is much more powerful than my macbook. \$\endgroup\$ Jul 29, 2015 at 19:28
  • \$\begingroup\$ I'm thinking it could be some difference between excel 2007 and mac excel 2011 \$\endgroup\$ Jul 29, 2015 at 19:29
  • \$\begingroup\$ Seems more likely to be related to the speed of the machine's network connection than anything else... Instead of using a querytable you might try using Workbooks.Open() and passing in the URL as the filename. \$\endgroup\$ Jul 29, 2015 at 20:25

2 Answers 2

2
\$\begingroup\$

I like the sound of using a swarm as suggested by @Rubberduck but I doubt that VbScript will run on a Mac. I thought I would make some general suggestions about your VBA code.

  1. When adding a QueryTable using VBA you should be aware that Excel creates both a data connection and a named range. If you are running a process that has a large number of loops this can result in lots of connections and named ranges. I suggest you delete them after you have got the data.

    Dim tempQueryTable As QueryTable
    
    qurl = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol
    qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
        "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
        Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Sheets("Data").Range("a1") & "&q=q&y=0&z=" & _
        Symbol & "&x=.csv"
    
    Set tempQueryTable = ThisWorkbook.Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _
        Destination:=ThisWorkbook.Sheets("Data").Range("a1"))
    
     With tempQueryTable
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
    
    ' do something with the data here
    ThisWorkbook.Sheets("Data").Range("a1").CurrentRegion.TextToColumns _
        Destination:=ThisWorkbook.Sheets("Data").Range("a1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, other:=False
    
     ThisWorkbook.Sheets("Data").Columns("A:G").ColumnWidth = 12
    
    ' delete the named range that gets created
    ThisWorkbook.Names(tempQueryTable.Name).Delete
    ' delete the query table
    tempQueryTable.Delete
    
    ' ... other code here
    
  2. Avoid looping over blocks of cells in Excel whenever possible. Perform a single operation on the whole range. So the loops where you write the Adj Close prices and the dates becomes:

    ' Copy the Adj Close prices
    'Dim z As Integer
    '        For z = 2 To numb_days + 1
    '            ThisWorkbook.Worksheets("Sheet1").Cells(RowIndex:=x, ColumnIndex:=z + 5) = ThisWorkbook.Worksheets("Data").Cells(RowIndex:=z, ColumnIndex:=7)
    '        Next z
    ThisWorkbook.Worksheets("Sheet1").Cells(RowIndex:=x, ColumnIndex:=7).Resize(ColumnSize:=numb_days).Value = _
        Application.Transpose(ThisWorkbook.Worksheets("Data").Cells(RowIndex:=2, ColumnIndex:=7).Resize(RowSize:=numb_days).Value)
    
    ' Other code here ...
    
    ' Copy the dates
    'Dim xyz As Integer
    '    For xyz = 2 To numb_days + 1
    '        Worksheets("Sheet1").Cells(1, xyz + 5) = Worksheets("Data").Cells(xyz, 1)
    '    Next xyz
    ThisWorkbook.Worksheets("Sheet1").Cells(RowIndex:=1, ColumnIndex:=7).Resize(ColumnSize:=numb_days).Value = _
        Application.Transpose(ThisWorkbook.Worksheets("Data").Cells(RowIndex:=2, ColumnIndex:=1).Resize(RowSize:=numb_days).Value)
    
  3. I have found that calling Application.WorksheetFunction can be quite slow. You can use the Rows.Count property to find out how much data you have. So the code to count the number of days can become:

    'numb_days = Application.WorksheetFunction.CountBlank(Worksheets("Data").Range(Worksheets("Data").Cells(2, 7), Worksheets("Data").Cells(1000, 7)))
    'numb_days = 1000 - numb_days
    numb_days = ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion.Rows.Count - 2 ' first row is headers, last row is empty
    
  4. You define a Worksheet variable but then never actually use it. Using such variables can make your code much easier to read.

    Dim DataSheet As Worksheet
    
    ' define this once outside the loop
    Set DataSheet = ThisWorkbook.Worksheets("Data")
    
    For x = start_ticker To end_ticker
        ' Lines such as this one become much easier to read
        'ThisWorkbook.Worksheets("Data").Cells.Clear
        DataSheet.Cells.Clear
    
  5. You can use the Timer function to work out the number of seconds a certian chunk of code takes. This helps you focus on the worst performing code without having to guess what is taking the longest to run. However, getting data from the internet is something that you have little control over.

\$\endgroup\$
1
  • \$\begingroup\$ Hey man thank you so much. You are a life saver. Deleting the contents of the query table fixed everything. I've received roughly 30 comments over the last two days regarding this code and you were the only one who was able to find the key problem with the code. You have some good knowledge! \$\endgroup\$ Jul 30, 2015 at 16:08
2
\$\begingroup\$

I don't know what would cause the large difference in time, but I found a couple minor factors:

  • Application.ScreenUpdating = False is performed in each loop (~4 000 times I assume)
  • Application.DisplayAlerts = False is not only performed in the same volume but also not negated in the scope of this question.
  • The lack of .Value2 has a potential performance demerit as discussed here.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.