Improving Calculation Performance in Excel–Final

There would be no final for performance tuning, as we always want faster models and I have enough tips to cover up to part 99.  But let’s stop here and I wish we learn the lesson that performance should be considered in the design on every Excel model.  Performance does not come by accident; we have to carefully craft it.  No one want to spend more than 10 seconds for the results and don’t even mention hours or days.  We don’t always need a faster machine and sometime even a faster machine won’t help a lot.  There are many useful resources that we can Google about and we would find most of them involve common sense.  We don’t have to have a PhD in computer science to understand the concepts; mainly remove unnecessary calculation and use a faster method if available.

How can we determine what method is faster?  We can use timer in Excel VBA but what about Excel functions?   There is a very useful set of macros that we can find in a Microsoft MSDN article, Excel 2010 Performance: Improving Calculation Performance written by Charles Williams, Decision Models Limited.  This article also discuss many great tips about performance tuning in Excel and you may want to visit Decision Models’ website because Charles has a wonderful profiling tools, FastExcel, to find and measure timing and memory bottlenecks in our workbook.

Let’s go back to the example in Improving Calculation Performance in Excel–Part 2 and illustrate how this macros can help us.

  • Select the range that we like to measure the performance, i.e. C2..C13

image

  • Press Alt+F8
  • Select RangeTime and click Run button

image

  • Now, we can see how long Excel took to perform the calculation.

image

  • And let’s repeat it on Col D

image

Only 12% gain?  Not a lot!  Well, it is because we only sums up 12 records.  Let’s see the performance gain when we have 1000 records.  It is 2.4 times faster!

image

image

What if we push further and review 5,000 records, then you can see the HUGE difference, i.e. 12 times faster!

image

image

How about the maximum limit of Excel?  1,048575 records.  Col D returned the results in less than 2 seconds.  How about Col C?  I went out to do 30 minutes jogging, came back to have a quick shower and breakfast… Excel is still very busy doing calculation, so I stopped it after an hour.

image

Col C is not scalable nor sustainable.  Initially, it may work and give us the correct results but as we have more data, the processing time would increase exponentially.  The fix (Col D) is very simple and effective that any Excel user can understand.

There may be time that we have to involve an IT consultant who can optimize our Excel model.  But if we do our homework then we may find ways to significant improve the performance of our Excel models.

Wish you all the success in performance tuning.  But if you need any help then please don’t hesitate to contact me at 416-804-6878 or chan_a@algconsultings.com

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

 

 

 

 

Improving Calculation Performance in Excel–Part 3

Who doesn’t use array in Excel VBA can skip this article.  But I guess most of us use array in VBA but not all of us understand that different array manipulation methods can impact the calculation performance.  Below is another dump calculation that tried to sum the total for each row in 3 different approaches.  The first one is my favorite, short and simple and I would try to avoid the last one because it would take longer time to type the code.

startTime1 = Timer()

For i = 1 To 1000000
    Total = 0
    For j = 1 To 20
        Total = Total + Table(i, j)
    Next
Next

endTime1 = Timer()

MsgBox sCalcType & " " & CStr(Round(endTime1 – startTime1, 6)) & " Seconds", _
        vbOKOnly + vbInformation, "Array Manipulation"

startTime2 = Timer()

For i = 1 To 1000000
    Total = Table(i, 1) + _
            Table(i, 2) + _
            Table(i, 3) + _
            Table(i, 4) + _
            Table(i, 5) + _
            Table(i, 6) + _
            Table(i, 7) + _
            Table(i, 8) + _
            Table(i, 9) + _
            Table(i, 10) + _
            Table(i, 11) + _
            Table(i, 12) + _
            Table(i, 13) + _
            Table(i, 14) + _
            Table(i, 15) + _
            Table(i, 16) + _
            Table(i, 17) + _
            Table(i, 18) + _
            Table(i, 19) + _
            Table(i, 20)
Next

endTime2 = Timer()

MsgBox sCalcType & " " & CStr(Round(endTime2 – startTime2, 6)) & " Seconds", _
        vbOKOnly + vbInformation, "Array Manipulation"

startTime3 = Timer()

For i = 1 To 1000000
    Total = Col01(i) + _
            Col02(i) + _
            Col03(i) + _
            Col04(i) + _
            Col05(i) + _
            Col06(i) + _
            Col07(i) + _
            Col08(i) + _
            Col09(i) + _
            Col10(i) + _
            Col11(i) + _
            Col12(i) + _
            Col13(i) + _
            Col14(i) + _
            Col15(i) + _
            Col16(i) + _
            Col17(i) + _
            Col18(i) + _
            Col19(i) + _
            Col20(i)
Next

endTime3 = Timer()

MsgBox sCalcType & " " & CStr(Round(endTime3 – startTime3, 6)) & " Seconds", _
        vbOKOnly + vbInformation, "Array Manipulation"

Below are the results but I am not going to tell you what approach is the quickest.  I want you to try the code yourself and see if you can reproduce similar results.  You can see the difference between the quickest and slowest approaches is nearly 300%.

image

image

image

So next time you use array in VBA, think about how you should write the code because they are not the same in term of performance!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Improving Calculation Performance in Excel–Part 2

In my last blog, I demonstrated how removed unnecessary calculation can significantly improve VBA performance, i.e. over 400%.  I want to show you how similar performance gain could be achieved in Excel worksheet as well.

Below is a very simple worksheet; anyone knows Excel can do it.  What is the big deal?

image

Here is the formula in column C.

image

Let me add another column which would return identical results.

image

What formula is in column D?

image

Which formula would you use?  It may not be matter for only 12 cells but if you apply column C and D  to 1 million rows, then the difference can be huge. Column C refers to n cells and add n-1 times; where column D only refer to 2 cells and add 1 time for each row.  I let you to do the maths to calculate how many cells that Excel needs to refer and add for column C if there is 1 million rows.

Again, it is something simple and effective.  When we design our model, we must always have performance in mind!  And if you are not sure which one performs faster, then time it.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Improving Calculation Performance in Excel–part 1

We can build sophisticated and CPU intensive models in Excel and some of them can take hours or even days to complete.  We can speed up the model by upgrading our computer, overclocking our CPU or optimizing our models.  Some of the optimization methods can be very simple and surprisingly effectively.  Let me use an example to illustrate.

The following VBA routine runs some dump calculation for 100,000,000 times.

 

Option Explicit
Public Sub Main()
Dim startTime As Date
Dim endTime As Date
Dim run As Long
Dim runs As Long
Dim AnnIntRate As Double
Dim Results As Double
runs = 100000000
AnnIntRate = 0.05
startTime = Now
For run = 1 To runs
    Results = run * (AnnIntRate ^ (1 / 365) )
Next
endTime = Now
MsgBox (startTime & endTime)
End Sub

 

It took 34 seconds to complete.

image

I could optimize the code and reduced the processing time to 8 seconds, i.e. roughly over 400% improvement.  How?

image

Option Explicit
Public Sub Main()
Dim startTime As Date
Dim endTime As Date
Dim run As Long
Dim runs As Long
Dim AnnIntRate As Double
Dim DailyIntRate As Double
Dim Results As Double
runs = 100000000
AnnIntRate = 0.05
startTime = Now
DailyIntRate = AnnIntRate ^ (1 / 365)
For run = 1 To runs
    Results = run * DailyIntRate
Next
endTime = Now
MsgBox (startTime & endTime)
End Sub


 

Power ^ and Division / require more CPU cycles than Multiply * .  Since AnnualRate ^ (1 / 365) is a constant within the for loop, we can replace the Power and Division with a double variable DailyIntRate and a Multiply.

There are many ways that we can optimize our Excel models; why do we want to wait for hours if we can get it done within minutes!

My computer has a Intel Q9400 CPU inside and I am using Excel 2010 64 bit.  You may find difference results on your computer because CPU can have much different architecture.

p.s. Thank squiggler to point out that my original post has a bug in it that didn’t return the same results.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 1: Performance Review

I had used VB6 / VBA for many years and I like them a lot!  Like most Windows programmers, I developed my first Windows application using Visual Basic ; I didn’t have to learn or understand Windows API which drastically reduced the learning curve.  However, technology has changed a lot in the last 10 years.  Microsoft stopped support VB6 in March 2008 and as of July 1, 2007, Microsoft no longer offered VBA distribution licenses to new customers. 

Yes, we can still find VBA in Office 2010 but what improvement has been introduced in the latest VBA?  We can now migrate Excel 4 macros to VBA – this is the only enhancement that was mentioned in Microsoft Technet , Changes in Excel 2010.  Does anyone still have any Excel 4 worksheet?

I don’t think Microsoft would get rid of VBA in near future but at the same time, is Microsoft going to invest any significant effort to improve VBA?  I seriously doubt it.  So do you want to use a development tools that didn’t have any major upgrade for the last 10 years?  Or do you prefer to use the latest development tool that allow you to simplify coding, debugging and deployment?  A tool that you can target multiple platforms, e.g. Windows, web, cloud and of course Office environment.

Let’s look at the difference between Visual Studio and VBA by first reviewing their performance.  I developed a very simple financial calculator using Excel VBA.  I ran it.

Option Explicit

Public Sub Main()

Dim startTime As Date

Dim endTime As Date

Dim run As Long
Dim runs As Long

Dim Age As Integer
Dim AgeStart As Integer
Dim AgeLimit As Integer

Dim InterestRate As Double
Dim MeanInterestRate As Double
Dim Variance As Double
Dim Scenarios As Integer
Dim Scenario As Integer

Dim i As Integer
Dim PV As Double

runs = 10000

AgeStart = 20
AgeLimit = 100

Scenarios = 500
MeanInterestRate = 0.05
Variance = 0.05

startTime = Now

For run = 1 To runs

    For Age = AgeStart To AgeLimit
   
        For Scenario = 1 To Scenarios
       
            InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
            PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate

       
        Next

    Next

Next

endTime = Now

MsgBox (startTime & endTime)

End Sub

image

It took 114 seconds to complete.

I copy and paste the whole program to Visual Studio 2010.  I compiled it and ran it again.

image

It took 58 seconds so it is nearly 2 times faster.

I modified couple lines in Visual Studio and ran it again.

image

Now it only took 23 seconds; nearly 5 times faster than VBA.  What did I change in the program?  Let’s look at it.

Imports System.Threading.Tasks

Module Module1

    Sub Main()
        Dim startTime As Date

        Dim endTime As Date

        Dim runs As Long

        Dim Age As Integer
        Dim AgeStart As Integer
        Dim AgeLimit As Integer

        Dim InterestRate As Double
        Dim MeanInterestRate As Double
        Dim Variance As Double
        Dim Scenarios As Integer
        Dim Scenario As Integer

        Dim i As Integer
        Dim PV As Double

        runs = 10000

        AgeStart = 20
        AgeLimit = 100

        Scenarios = 500
        MeanInterestRate = 0.05
        Variance = 0.05

        startTime = Now

        Parallel.For(0, runs, Sub(run)

                                  For Age = AgeStart To AgeLimit

                                      For Scenario = 1 To Scenarios

                                          InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
                                          PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate

                                      Next

                                  Next

      End Sub)

        endTime = Now

        MsgBox(startTime & endTime)

    End Sub

End Module

Do you know what the changes are?

The CPU inside my computer is an Intel Q9400 which is a quad core processor.  However, the first 2 runs (VBA and VS 2010) only utilized 1 core.  Visual Studio 2010 supports parallel programming which would automatically make use of all cores on my system.  The code to utilize parallel programming is quite simple;  I just replaced the For statement with Parallel.For, then Visual Studio would execute the program on all cores rather than single core.

Without spending any effort on optimization, the performance gain to migrate from VBA to VS 2010 is already enormous, i.e. nearly 500%.

  Execution Time (Seconds)
Excel VBA 114
Visual Studio 2010 (w/o Parallel Programming) 58
Visual Studio 2010 (with Parallel Programming) 23

Someone may argue my program does not reflect a real financial application because it does not include any database input / output (I/O).  They are right!  My program does not have any I/O and I/O may not have such significant performance gain.  However, today’s financial model may involve hundred to thousand scenarios and each scenario can contain a few million calculations, therefore, the effort to I/O a small piece of data would be very minimal.

The purpose of this demonstration is to illustrate a performance gain can be easily achieved by migrating to Visual Studio 2010.  My next blog would be addressing the different programming methodology between VBA and VS, i.e. Object Based and Object Oriented.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Should I buy a new computer?

Is your computer getting slower and slower?  It used to take only couple minutes to start but now it takes nearly 10 minutes.  You tried:

  • DiskCleanup to free up disk space;
  • Disk Defragmenter to speed up data access;
  • Error Checking utility to detect and repair disk errors; and
  • Security software to clean your computer against malware, spyware and virus.

Nothing worked!  Every morning, you can enjoy your coffee and donut while waiting for your computer to start.  Should you buy a new machine?  Before you take out your credit card, you may want to try 1 more thing’; disable some of your startup applications.

Startup Applications

When you start Windows; some applications would automatically start with Windows.  It is not uncommon to see a computer with over 100 sartup applications (or 200+ at some extreme cases).  I bet you probably don’t know most of them if you go over them in System Configuration dialogue.  Do you need them all?  I highly doubt it!  You should seriously consider disabling them or even uninstall them!

You may want to discuss with your IT consultant on what application should be loaded during startup.  I can often disable 90% of them and regain the power of my client’s machine.

You can review your startup applications list by:

  • Enter msconfig at command prompt.
  • Select Startup tab at System Configuration dialogue.

image

It is very simple to disable startup applications; however, the bigger question is why these startup applications were installed at the first place?  Does your company have change management strategy in place?  Can your staffs install anything they want on their computer?  Do you know what applications were installed in your company?

Andrew Chan is the owner and founder of ALG Inc.