Python loop slower than Excel VBA?

Question

I ran a little test between excel (VBA) and python performing a simple loop. Code listed below. To my surprise vba was significantly faster than python. Almost 6 times faster. I though that due to the fact that python runs through the command line the performance would be better. Do you guys have any comments on this?

Python

import time
import ctypes  # An included library with Python install.
start_time = time.time()

for x in range(0, 1000000):
    print x

x = ("--- %s seconds ---" % (time.time() - start_time))
ctypes.windll.user32.MessageBoxA(0, x, "Your title", 1)

Excel (VBA)

Sub looptest()

Dim MyTimer As Double

MyTimer = Timer

Dim rng As Range, cell As Range
Set rng = Range("A1:A1000000")

x = 1
For Each cell In rng

    cell.Value = x
    x = x + 1
Next cell


MsgBox Timer - MyTimer
End Sub

Answer 1

Your two code samples are not doing the same thing. In the Python code, the inner loop has to:

• Ask for the next number in range(0, 1000000).
• Display it.

In the VBA code, Excel has to:

• Ask for the next cell in Range("A1:A1000000") (which has nothing to do with Python ranges).
• Set the cell.Value property.
• Run through various code Excel executes whenever it changes a cell.
• Check to see if any formulas need to be recalculated.
• Display it.
• Increment x.

Let's rewrite this so the Python and VBA loops do the same thing, as near as we can:

Python

import time
import ctypes
start_time = time.time()

x = 0
while x <= 1000000:
    x = x + 1

x = ("--- %s seconds ---" % (time.time() - start_time))
ctypes.windll.user32.MessageBoxA(0, x, "Your title", 1)

VBA

Declare Function QueryPerformanceCounter Lib "kernel32" (t As Currency) As Boolean
Declare Function QueryPerformanceFrequency Lib "kernel32" (t As Currency) As Boolean

Sub looptest()
    Dim StartTime As Currency
    QueryPerformanceCounter StartTime

    x = 0
    Do While x <= 1000000
        x = x + 1
    Loop

    Dim EndTime As Currency
    QueryPerformanceCounter EndTime
    Dim Frequency As Currency
    QueryPerformanceFrequency Frequency

    MsgBox Format$((EndTime - StartTime) / Frequency, "0.000")
End Sub

On my computer, Python takes about 96 ms, and VBA 33 ms – VBA performs three times faster. If you throw in a Dim x As Long, it performs six times faster.

Why? Well, let's look at how each gets run. Python internally compiles your .py file into a .pyc, and runs it under the Python VM. Another answer describes the Python case in detail. Excel compiles VBA into MS P-Code, and runs it under the Visual Basic VM.

At this point, it doesn't matter that python.exe is command-line and Excel is GUI. The VM runs your code, and it lives a little deeper in the bowels of your computer. Performance depends on what specific instructions are in the compiled code, and how efficiently the VM runs these instructions. In this case, the VB VM ran its P-Code faster than the Python VM ran its .pyc.

Answer 2

The slow part about this is the print. Printing to the console is incredibly slow, so you should totally avoid it. I assume that setting cell values in Excel is just way faster.

If you want to compare computation speed you should not have any I/O within the loop. Instead, only calculate the time it took to process the whole loop without doing anything inside (or doing something simple like adding a number or something). If you do that, you will see that Python is very fast.

Answer 3

It varies with the computation you need to perform and it is difficult to give a proof which is faster in a simply way, with a simple comparison. So I will share my experience in a generic way, without proofs, but with some comments on things that may generate a big difference.

In my experience, if you compare a simple for loop with exactly the same code between pure python and pure VBA, VBA is around 3 times faster. But nobody do the same in different languages. You have to apply each language best practices.

If you apply VBA best practices you can make it even faster through declaring the variables and other similar optimizations not available in python. In my experience this can make code around 2-3 times faster. So you can make VBA code around 6-9 times faster than a simple for loop in python.

On the other hand, if you apply python best practices, you won't generally write a regular for loop. You will use list comprehension or you will use numpy and scipy, which run in compiled C libraries. Those solutions are much faster than VBA code.

In general, if you perform complex matrix calculations you can do with numpy and scipy, python will be faster than VBA. In other cases, VBA is faster.

In python, you can also use Numba, which adds a bit of complexity to the code, but it helps generating compiled code and handles running it in the GPU. It will make your code even faster.

This is my experience with pure computation mainly involving internal arrays. I haven't compared performance for I/O with GUI, external files and databases, network communication or API.