The Spreadsheet Optimization Process
The process of optimizing spreadsheets focuses on three main areas:
There are many known problems that affect spreadsheet performance, stability and
maintainability:
Unpredictable asynchronous calculations significantly increase CPU usage
and reduce Excel's responsiveness to user input.
Many Excel workbooks contained links to external workbooks. Updating these
links creates another series of calculations, reduces memory by opening another
workbooks and creates unnecessary network traffic if the external workbook is
located on a network drive.
Numerous calls to the same function nested within other functions
throughout numerous cells in an Excel worksheet causes redundant and
unnecessary calculations.
Parsing values from Reuters and Telerate pages within individual
cells of an Excel worksheet is error prone and impossible to maintain.
Many macros written to update and maintain financial models were
recorded and inefficient and do not work.
Most of the Excel workbooks have evolved over time and numerous changes
have occurred. As a result many of the named ranges are no longer valid and
refer #REF. These spurious ranges make the worksheet unstable and subject
to crashing.
Use of volatile functions, which recalculate whenever calculation
occurs in any cells on the worksheet. Their use is a contributing factor
to excessive calculation within a spreadsheet.
Willow Solutions has developed a series of procedures used to solve these problems.
Use RtUpdate to Eliminate Unpredictable Calculations
RtUpdate retrieves real-time financial information at
a specified frequency.
RtUpdate retrieves real-time data into a table.
The cell containing the RtUpdate function displays the time of the last update.
When real-time data changes the entire table is updated a specified interval all at once.
The RtUpdate formula has an argument which can reference
a single cell and allow the user to easily turn all updating of real-time financial
information on and off. We have added a Control Panel worksheet to many of the optimized
worksheets, which allow the user to stop real-time updates as well as control contributions.
It also standardizes date, time and market maker name for contributions.
Use DDE Arrays to Eliminate Unpredictable Calculations
The Reuters Personal Data Dictionary (PDD) application,
which is the server application to bring in real-time data into Excel, has an updates
setting which allows real-time data to be updated at a specified frequency.
The PDD update settings allow the user to control the
rate at which real-time data is updated in all open workbooks. Setting the worksheets
to update at a specified interval allows the user to control when updates occur.
Besides greatly reducing the amount of calculation, it allows the user to enter data
on the worksheets and work with other applications.
Using a DDE array formula retrieves real-time data into
several cells with a single link, similar to the RtUpdate table. When real-time data
changes the entire group of cells are updated all at once.
The DDE array formula creates a single link for real-time
data in many cells. It reduces the number of external links and reduces unnecessary
network traffic.
Remove Links to External Workbooks
Making a workbook self-contained by removing links to
external workbooks reduces strain both on the workstation and network resources.
In order to update a link to an external workbook it is necessary to open the workbook.
This reduces the amount of memory available and causes another series of calculations.
To alleviate these problems, we have imported the linked
section of external workbooks into the dependent workbook.
Eliminate Redundant Calculations
Rather than have numerous cells calculate the same function,
such as TODAY() many times, we replaced the function within the cell or nested within
another function with a reference to a cell which contained the calculated value of the function.
Using this method allows the function to be calculated just
once rather than over and over again when it is nested within a formula.
Moving All Real-time Page Data to Single Page
To make the financial models used in Excel workbooks less
error prone and to allow several users to work with to understand, and to maintain,
real-time page data should be moved to a single column on a separate worksheet.
The formulas to perform the parsing should be put in
columns adjoining the column with the real-time page data.
These cells should be named and then referenced by
financial functions that perform calculations on the parsed data.
A template has been created to organize the page-based data.
Organize and Rewrite Existing Macros
Recorded macros use cell selection, do not use error
checking and do not turn off screen updating. These macros will usually not work
correctly or not work at all if worksheets are altered.
These macros were rewritten to remove these
inefficiencies. In some cases they were no longer being used and were eliminated.
Since many perform the same functions such as copying
formula values to various sections of the workbook, they can be replaced with a
common set of tools.
Remove Invalid Range Names
|
