Is Excel our only option?

Microsoft Excel is a very popular spreadsheet program that we use to analyze data.  It has many great analytical features, e.g. Pivot Table, What-if Analysis, Goal Seek, Solver, and Analysis ToolPak; and there are many more that I can’t cover here.  Excel 2010 has introduced even more powerful features, e.g. Slicer, Sparklines, and PowerPivot.  I have published an article about these Excel 2010 new features in Society of Actuaries CompAct 2010 October Issue.

Excel is a great Swiss army knife that has many other talents.  We can use Visual Basic for Applications (VBA) to automate repetitive Excel tasks.  Excel 2010 64 bit allows us to save data that is larger than 2 GB.  We can also create reports / dashboard.  Excel is so flexible and powerful that we can do a lot more than just analytical works.  I know someone even used Excel to draw floor plan and draft legal contract.  I would like to take this opportunity to discuss if we should use Excel to develop business critical applications or store huge amount of data.

VBA

Most of us use VBA in Excel to automate repetitive tasks; e.g. automatically import data and generate all our month end reports.  We can save a lot of efforts and focus on analysis.  We can also use VBA to enhance Excel, e.g. develop a simple annuity calculator or a sophisticated risk management system.

However, Microsoft did not add any major enhancement to Excel VBA for nearly a decade and as of July 1, 2007, Microsoft stopped offering VBA licenses to new customers.  During the last 10 years, technology and our business world have significantly changed.  We can now buy a hexa core CPU for less than US$ 200; cloud computing and smart phone are getting popular.  New regulations likes Sarbanes-Oxley requires us to regulate and audit important corporate applications and systems.  Can Excel VBA handle all these changes?  What other options do we have?  There are many options but I am going to just focus on Microsoft products since we are familiar with them.

I wrote an article about Visual Studio (VS), and demonstrated how migrating a VBA application to VS could achieve nearly 500% performance gain on my 2 years old, quad core machine.  All I did was to modify 2 lines of code! 

VS has provided many new features; e.g. we can develop applications for our desktop, smart phone, and even cloud; support new technologies like multi core processor or computation on video card.  Even if we don’t need such technologies, we can increase our code effectiveness because Visual Basic .Net has fully supported object oriented programming.  We have much better version control and deployment options to satisfy our statutory requirements.

I also documented some discussions on when we should use VBA or VS in another article.

VS has  a lot of great features and the best way to understand our options is to try it ourselves.  We can download a free copy of Visual Studio Express from Microsoft.

Both VBA and VS have many good features, but the best solution to a problem might not involve any programming at all.  I know a financial analyst who used VBA to optimize his financial model; then he later discovered Solver could do virtually everything he developed.  Before you start any programming, make sure that there is not a simpler way.

Data

Excel has many features to manipulate data, e.g. sort and filter data, Lookup and Database functions, tools to consolidate and validate data, remove duplicated data.  Excel has provided us everything we need to manage our data, has it?

Excel is not scalable.  If we only have a few thousand records or a few MB of data, Excel works great and performs quick!  However, when you have a few millions records or a few hundred MB of data, then most Excel operations would take a long while to process; especially if your machine only have 1 – 2 GB of physical memory.

It is difficult to implement audit log in Excel.  We can control who can view, edit or modify the Excel workbooks.  But once we let our users to have access to the spreadsheet, it is very hard to monitor their usage; especially in a multi users’ environment.

Excel does not offer good multi users support.  If we have a few people worked on the same worksheet for a morning, we would understand the limit of Excel.  We won’t see each other’s changes until they save their copy.

If we can store all our data in a single worksheet, Excel is great for such flat structure.  However, if we have a lot of data and our data has to be stored in multiple worksheets, then you would find VLOOKUP and other LOOKUP functions can be quite tricky to setup.

Relational database management systems (RDBMS) such as Access, DB2, Oracle or SQL Server are more appropriate if we:

• Have a lot of data and grow fast
• Want to control our data
• Support multi users concurrent access

Excel can analyze data that is not stored in Excel.  Data connections allow us to import data into Excel with a few mouse clicks and we can even create Pivot Table directly from SQL Server.  Since our data is not stored within Excel, we won’t accidently modify the data while we analyze the data.

Again, we can download a free copy of Microsoft SQL Server Express for trial and evaluation.  You can follow the instructions in my blog to install SQL Server Express.  SQL Server Express allows us to store data up to 10 GB.  If we ever reach the limit, then we can consider to buy the standard version.

Conclusion

Excel is great; it has many flexible and powerful features to analyze data, manipulate data, visualize data and automate business processes.  However, we have to understand that there are other options and we have to evaluate the pros and cons of such options.  We need to use the right tools to do the right jobs to maximize our productivity. 

These options are FREE and they are from Microsoft, the same company which has developed, marketed and supported Excel!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!