Re: What is the best way to handle large excel files? Pedro Machado Aug 22, 2013 3:29 PM ( in response to Saurabh Bharadwaj ) I haven't done any 'scientific' benchmarks, but my experience is that if you have a lot of data and a good database server, you are better off connecting directly to the database. Write Block of Data in a single command. Many Excel VBA programs write data cell by cell to Excel. If we have 500,000 records and each record has 10 columns, then we would have 5,000,000 writes. There are VBA functions that can dump all of the data to Excel in a single command, e.g., CopyFromRecordset.
By Andrew Chan Excel is probably the most popular business software application. As actuaries, we have used Excel to develop applications that handle data analysis, reports, models and many other business activities. However, many Excel applications do not have proper design and therefore suffer from poor performance, are difficult to enhance, and are error prone. I have migrated many Excel applications to enterprise systems and have seen many common problems. This article is focused on Excel application design and how to build a better Excel application. Reinventing the Square Wheel Before developing any application, we should always conduct due diligence to make sure it is cost effective to build an application and whether this is the best solution. We can use VBA to import data from a PDF report, develop an optimization model, predictive model, or a Monte Carlo simulation, etc.
But most business users do not have the necessary skills or training to develop a high performing, well designed, robust application. The development cycle could take months or even years. They may not have the infrastructure in place to do a proper software development life cycle.
Most Excel applications are developed, maintained, and operated by a single person. What if the application owner leaves the company?
Are we sure that there is no commercial software available? Don't Jump to Coding “ Without requirements or design, programming is the art of adding bugs to an empty text file. ” - Louis Srygley This is probably the most common mistake; end-user computing (EUC) developers believe they know what the requirements are and start coding right away.
The truth is, most EUC developers only remember a portion of the requirements. This eventually results in a development cycle that takes entirely too long. The project schedule may double or triple, and we still don't see the end of development. It is always a good idea to prepare a peer-reviewed requirements document.
Once we confirm the requirements, we can start the design. If we are a seasoned system consultant, then we may consider evolutionary design, e.g., design the system as we develop the system. However, most EUC developers are part-time programmers and do not have proper programming training. Evolutionary design can be a disaster. We might find that it is getting more and more difficult and time consuming to develop the application.
Obviously this is Apple’s preferred method of distribution but I’ve found many application developers aren’t using it yet. The OS X “maximize” button doesn’t seem to actually work properly and infuriatingly tends to just stretch an app up and down. Of course, this isn’t actually explained to you and at first I was just running everything out of my downloads folder. That said, applications that are installed using the Mac App Store simply install themselves without requiring any drag-ad-drop or DMG mounting. How to switch for windows to mac os and back. I’m still yet to figure out how to manage windows properly without Windows’ “snap” functionality which allows you to drag an application to the side of the screen and it will automatically take up half of the screen.
Over time, we are going to add more bugs than new features without a proper design. If we do a planned design before programming, then we can discover many of the issues that we should avoid and the application will be much more structured and easier to enhance in future. Simple is Beautiful “ Programs must be written for people to read, and only incidentally for machines to execute.” - Abelson/Sussman Excel Formulas Most Excel users have difficulty understanding their own Excel formulas if it has not been looked at for a few months. Have you seen an Excel formula that had 10 nested if functions? Or one with more than 100 different processes within a single formula, e.g. Lookup data, compare results, calculations, etc.
This is not an Excel formula, but a mini program inside an Excel cell. It could take another person a whole day to understand what it did. Perhaps some logic was copied and pasted in another part of the Excel workbook. Sooner or later, we would need to enhance or fix the logic, good luck to us trying to find it.
Each Excel formula should only have one simple responsibility. We should divide a complex formula into multiple simple formulas, e.g.: • A set of Excel columns that lookup all the required data; • A set of Excel columns to handle comparisons; • A set of Excel columns to handle calculations; and • A few more Excel columns to consolidate all the above columns. This approach offers better transparency; we can review the model easier. We do not need to copy and paste the same logic multiple times and it would be straightforward to reuse it in another Excel formula.