Hey everyone,
A couple months ago, I received a couple of Cyber Monday deals from O’Reilly and Apress offering 50% off all e-books. I couldn’t resist and I bought about 10 books, including a set of 5 called the “Data-Science Starter Kit” which includes tutorials on R and data analysis. One of the books I purchased was Alexander and Clark’s Excel & Access Integration, which covers basic connectivity between the two programs along with more advanced techniques such as VBA/SQL/ADO integration. Learning how to use the latter technique was the main reason I decided to purchase the book. We actuaries are well-versed in basic maths and finance, but when it comes to programming and database management, as a group we aren’t that strong. However, one of our strongest traits is being able to teach ourselves, and many of the most skilled programming actuaries I know are self-taught (actually, it is believed that most programmers in general are autodidacts).
Actuaries spend a good chunk of their time (possibly most) working with Excel and Access, and while most of them eventually become proficient with both softwares, very few become adept at integrating the two programs efficiently to make the best use of their time. Learning to do so takes a non-trivial investment of time and effort – first of all, being proficient with the interfaces of the two programs is a must. Second, the actuary must learn VBA to familiarize himself with the language’s objects, properties, and methods (and that’s if the actuary is already familiar with object-oriented programming). Third, the actuary must learn SQL to efficiently query tables. Finally, the actuary must learn ADO to simultaneously manipulate Excel and Access objects, and to be able to write SQL queries within the VBA environment.
To a junior actuary, this can be a daunting task. Not only must he keep up with the deadlines from his regular work, but he must also study mathematics for his credentialing exams. Fitting in additional IT coursework is a luxury. However, in my opinion it’s well worth the effort. By the time I purchased this book, I was on the 3rd step of the process I had mentioned earlier – I was learning SQL and slowly weaning myself away from the Design View in Access. I started reading the book at the beginning of this month and finished it last afternoon, and in timing myself I totaled about 21.5 hours over 374 pages. Here’s what I think:
Experts can skip to Chapter 8
The first 7 chapters cover basic integration techniques using the Excel and Access GUIs, mostly through the ribbons of each program. Some of these techniques involve linking tables and queries, along with creating reports and basic macros in Access. Chapter 7 gives a brief introduction to VBA, but doesn’t go as in-depth as Walkenbach’s text (which is over 1000 pages long). In my opinion, these chapters are good for those looking for a refresher in the basics, but novices should look elsewhere as these chapters might not be detailed enough to give a comprehensive review of Excel and Access. On the other hand, experts looking for a quick introduction on ADO might find the first 7 chapters trivial, and should be able to start on chapter 8 without any trouble if they have an upcoming deadline to meet.
Chapter 8 is where the book really shines. I view ADO as the “missing piece” that analysts need to integrate these two programs. The example subroutines provided with the included files are clear, easy to understand, and come with plenty of comments that explain how each step works. The macros are ready to run, and you can see how it’s possible to say, create a subroutine that can output 50 queries into a report with no human intervention.
The last two chapters focus on XML and integrating Excel and Access with other Microsoft applications such as Word, PowerPoint, and Outlook. I don’t use these programs heavily, but the examples were straightforward and understandable.
Some Caveats
Not all of the examples work. I found that one of the provided tables was missing a field that I needed to run an example using MSQuery. Furthermore, some details within the provided files were inconsistent with what I read in the text. For instance, some of the subroutine names were different, along with the names and extensions of some files. The last thing I didn’t like about the book was the overuse of some buzzwords. However, this book is hardly the worst offender I’ve seen, and overall I’d rate it as an excellent book and a invaluable reference for any actuary’s library.