Calculation speed: Difference between revisions
(I have added some context to the Calculation speed page so that it doesnt start with a person but instead its reason for inclusion.) |
m (Fixed typos) |
||
| Line 1: | Line 1: | ||
Calculation speed is important as slow models are | Calculation speed is important as slow models are inefficient to add to and refine while also being frustrating to use. Slow models can lead to both users and developers switching tasks during an activity or losing their train of thought. | ||
[https://mvp.microsoft.com/en-us/PublicProfile/4025136?fullName=Charles%20Williams Charles Williams] generously shares information on improving Excel calculation speed (as well as VBA processing time) and his [http://www.decisionmodels.com/ Decision Models] and [https://fastexcel.wordpress.com/ Fast Excel] websites are considered by a lot of experienced modellers to be the best reference point on the topic. It is worthwhile for anyone who spends a lot of time modelling in Excel to go through those sites. A good starting point is to read the following pages in full: | [https://mvp.microsoft.com/en-us/PublicProfile/4025136?fullName=Charles%20Williams Charles Williams] generously shares information on improving Excel calculation speed (as well as VBA processing time) and his [http://www.decisionmodels.com/ Decision Models] and [https://fastexcel.wordpress.com/ Fast Excel] websites are considered by a lot of experienced modellers to be the best reference point on the topic. It is worthwhile for anyone who spends a lot of time modelling in Excel to go through those sites. A good starting point is to read the following pages in full: | ||
Latest revision as of 13:23, 20 February 2022
Calculation speed is important as slow models are inefficient to add to and refine while also being frustrating to use. Slow models can lead to both users and developers switching tasks during an activity or losing their train of thought.
Charles Williams generously shares information on improving Excel calculation speed (as well as VBA processing time) and his Decision Models and Fast Excel websites are considered by a lot of experienced modellers to be the best reference point on the topic. It is worthwhile for anyone who spends a lot of time modelling in Excel to go through those sites. A good starting point is to read the following pages in full:
The information that Charles shares is for a general technical audience - it is not solely dedicated to financial modelling. Financial models often have calculation characteristics that are outside broader norms for Excel workbooks, including:
- Multithreaded calculation: In most Excel workbooks generally, multithreaded calculation is usually faster than single threaded calculation. In financial models, the reverse is often true. It can go one way or the other (often by wide margins) and, if calculation speed is a consideration in your model, it is definitely worth testing.
- Smart recalculation vs forcing full calculations: In most excel workbooks generally, Excel's smart recalculation engine is plainly helpful because creating and maintaining the dependency tree is very quick and it improves calculation speed. In financial models, maintaining the dependency tree often takes longer and the smart recalculation engine often only provides a small improvement in calculation speed (sometimes it even makes the calculation speed worse), so there is commonly a stronger argument for forcing full calculations in financial models. That said, common financial modelling workflows are also particularly sensitive to other downsides of forcing full calculations (i.e. trace dependents becoming unreliable and charts generally not updating).