Friday, March 4, 2011

Do you have the Excel spreadsheet from Hell…

Do you have an Excel spreadsheet that takes hours or day to complete?

Do you have that really fast computer with the note on it that says "Do not touch" while the computer working doing complex calculations complex..


Have you played the game of buying faster and faster computer to get you excel spreadsheet processing done faster

Yes you could go to Excel 2010 version 64 bit to address more memory, but if I am using custom XLL UDF there may not be a version of the custom UDF for 64 bit so what one to do ? And does that make my spreadsheet much faster? Excel allows you to create custom functions, called "User Defined Functions" (UDF's) that can be used the same way you would use SUM() or some other built-in Excel function or I can user Automation add-ins are suitable if your UDF deals a lot with the Excel object model; XLL add-ins are faster in financial and mathematical calculations. Note however that native code XLL add-ins work faster than managed UDFs.

I have clients who have these problems and have just resolved themself to it is just going to take day to get the data out; for example;

Insurance company

• Insurance company who need to do reserve requirements calculation which answer the question do the company have the minimum reserves require buy regulators’ this is based on Hundreds of thousands of policies and can take 4 weeks to calculate each monthly.

• A controller at big company who doing, what if scenarios on budgets and forecasting the same formulas over and over again and the process takes 1 week s each time

• Assist with Monte Carlo simulation for risk analytics and other suitable scenarios in Financial Services and Energy trading in Oil & Gas industry verticals.

If you look at your company you probably find this is an issues for someone who user the big, complex, spreadsheet I have a solution for you!

HPC Services for Excel 2010

Here is a way to have MUTIPLE Computers share the workload

*** What new is now you can use Excel 2010 PLUS edition and have multiple computers share the workloads ***

HPC Services for Excel 2010 - HPC is part of Microsoft Technical Computing solution

HPC stand for High Performance Computing

HPC is a group of computers or a cluster of computer consists of several servers networked together where each server in the cluster performs one or more specific tasks. Cluster components include Head Nodes, and Compute Nodes, Job Scheduler and sometimes Broker Nodes (for SOA enabled clusters.)


(Graphic from Microsoft HPC site)

Head Node - is point of management and job scheduling for the cluster. It provides failover and controls and mediates access to the cluster resources.

Compute Node(s) -Carries out the computational tasks assigned to it by the job scheduler.

There are many ways that you can run a cluster, here are the methods.

Method 1: use 7 computer that at night or during the week end can be used as computer nodes

Method 2: use Widows Azure as a cloud solution

Method 3: Build a Windows HPC cluster in house

To learn more about HPC and to use HPC with Excel go here

I will be creating more Blogs detailing this solution.