There’s a lovely piece in Pieria about a data visualisation exhibition at the British Library, positing John Graunt’s analysis of London deaths as an early spreadsheet.
And yes, there were some errors in it.
Categories
Tag: spreadsheet
Categories
The right tool?
The next time you notice something being done in Excel where you work, take a moment to question whether it’s the right tool for the job, or whether you or someone in your organisation is a tool for allowing its use.
No, not my words, but from the FT’s consistently excellent Alphaville blog. The point is, it’s easy to use Excel. But it’s very hard to use Excel well.
There are many people out there who can use Excel to solve a problem. They knock up a spreadsheet with a few clicks of the mouse, some dragging and dropping, a few whizzo functions, some spiffy charts, and it all looks really slick. But what if anything needs to be changed? Sensitivity testing? And how do you know you got it right in the first place? Building spreadsheets is an area in which people are overconfident of their abilities, and tend to think that nothing can go wrong.
Instead of automatically reaching for the mouse, why not Stop Clicking, Start Typing?
But we won’t. There’s a huge tendency to avoid learning new things, and everyone thinks they know how to use Excel. The trouble is, they know how to use it for displaying data (mostly), and don’t realise that what they are really doing is computer programming. A friend of mine works with a bunch of biologists, and says
I spend most of my time doing statistical analyses and developing new statistical methods in R. Then the biologists stomp all over it with Excel, trash their own data, get horribly confused, and complain that “they’re not programmers” so they won’t use R.
But that’s the problem. They are programmers, whether they like it or not.
Personally, I don’t think that things will change. We’ll keep on using Excel, there will keep on being major errors in what we do, and we’ll continue to throw up our hands in horror. But it’s rather depressing — it’s so damned inefficient, if nothing else.
Categories
Spreadsheet use in investment banks
A white paper from Lepus Consulting on The Management of Spreadsheet Use in Financial Services. Despite the title, it considers only investment banks. It’s mainly anecdotal evidence from a survey (no numbers), with a short guide to best practice.
Categories
Web based spreadsheet
Another web-based spreadsheet. I don’t know how it compares to Google’s.
Categories
Excel as a swiss army knife
I’ve recently been involved in two surveys looking at what software actuaries use, and how they use it.
The first was organised by the GIRO working party on software use in general insurance, which I chaired. We got over 700 responses to our online survey, which was great. As we describe in the report, Excel is by far the most popular software, used by about 90% of the respondents.
The second survey was one that I ran myself, looking at software use in ICAs. There were 45 responses, from both life and non-life actuaries. Again, Excel was overwhelmingly popular: only one respondent doesn’t use it for preparing ICAs.
One theme that emerged from both surveys is the lack of informed choice. I really get the impression that many people use Excel just because it’s there, and because they can make it do what they need. However, it often takes a surprising amount of hard work. It’s a bit like using a swiss army knife to build a house; it probably can be done, but it’s not what you’d choose.
This was really brought home to me at the workshop we presented at the GIRO conference last week, on the results of the survey conducted by the working party. Presumably the people there were somewhat interested in software issues, and might be thought to be more informed than the run of the mill actuary. However, there were at least a couple who were very surprised when we said that we were disappointed at how few people used special purpose statistical software, given how poor the standard statistical functionality is in Excel.
Well, it’s definitely poor if you are operating in the tails of distributions. Microsoft admits that there are shortcomings in some of its algorithms, but basically says that it doesn’t matter much as everything is OK as long as you are not in the tails. But that’s exactly where most actuaries are operating. There are several useful descriptions of what the problems are. One that is especially annoying in some organisations is that the functions were changed in Excel 2003. If there are several different versions of Excel in the organisation, the results calculated by a spreadsheet can vary depending on who edited the spreadsheet most recently.
The random number generator in Excel isn’t brilliant, either. A new random number generator was implemented in Excel 2003, but in the first release some of the numbers it generated were negative, instead of in the [0, 1] range as advertised (this has since been fixed in a patch). The generator that was used in earlier versions of Excel was rather less random. If you are doing serious stochastic work, you should make sure that the generator you are using is sufficiently random. There are a number of add-ins available that provide more sophisticated algorithms than that used in the standard Excel one.