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!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

3 Responses to Improving Calculation Performance in Excel–part 1

  1. Squiggler says:

    With Excel reliant on FPU, there is hardly any difference between Divide and multiply :-

    Public Sub Main()
    Dim startTime As Double
    Dim endTime As Double
    Dim startTime1 As Double
    Dim endTime1 As Double

    Dim run As Long
    Dim runs As Long
    Dim Results As Double

    runs = 1000000

    startTime = Timer
    For run = 1 To runs
    Results = run ^ (1 / 365)
    Next
    endTime = Timer

    startTime1 = Timer
    For run = 1 To runs
    Results = run ^ (1 * 2.73972602739726E-03)
    Next
    endTime1 = Timer

    Debug.Print ((endTime – startTime))
    Debug.Print ((endTime1 – startTime1))
    End Sub

    I used 1* to make it fair and 1/365 pre-calculated, most of the speed saving in the example above is by removing the ^ from the loop, and since 1^(1/365) is always 1 both examples calculate different results, therefore its not really an optimization!

    startTime3 = Timer
    DailyRate = 1/365
    For run = 1 To runs
    Results = run ^ (DailyRate)
    Next
    endTime3 = Timer

    Would be an optimization of that routine, but not much of a saving!

    Debug.Print ((endTime2 – startTime2))

    • Andrew Chan says:

      Squiggler,

      Thank you for finding the bug for me. You are right that it didn’t return the same results. Now I fixed it. But the point I want to make is still valid that we can improve Excel calculation performance with some simple but effective was.

      I would do another blog to compare Division and Multiply but I believe you are right that we would not see as much improve as ^.

      Thanks again.

      Your sincerely,

      Andrew Chan.

  2. GaryFPatton says:

    OK, Andrew, your creativity & wisdom has earned you my recommendation to all my business friends & clients for the coaching help most of us need to release the REAL power in Microsoft ‘Excel’ and Open Office ‘Spreadsheet’.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: