Variations On Approximation – An Exploration in Calculation

Link: https://www.soa.org/news-and-publications/newsletters/compact/2014/january/com-2014-iss50/variations-on-approximation–an-exploration-in-calculation/

Graphic:

Excerpt:

Before we get into the different approaches, why should you care about knowing multiple ways to calculate a distribution when we have a perfectly good symbolic formula that tells us the probability exactly?

As we shall soon see, having that formula gives us the illusion that we have the “exact” answer. We actually have to calculate the elements within. If you try calculating the binomial coefficients up front, you will notice they get very large, just as those powers of q get very small. In a system using floating point arithmetic, as Excel does, we may run into trouble with either underflow or overflow. Obviously, I picked a situation that would create just such troubles, by picking a somewhat large number of people and a somewhat low probability of death.

I am making no assumptions as to the specific use of the full distribution being made. It may be that one is attempting to calculate Value at Risk or Conditional Tail Expectation values. It may be that one is constructing stress scenarios. Most of the places where the following approximations fail are areas that are not necessarily of concern to actuaries, in general. In the following I will look at how each approximation behaves, and why one might choose that approach compared to others.

Author(s): Mary Pat Campbell

Publication Date: January 2014

Publication Site: CompAct, SOA

Dataviz Horror Story: How I Crashed the Top Exec’s Email

Link: https://nightingaledvs.com/dataviz-horror-story-how-i-crashed-the-top-execs-email/

Graphic:

Video:

Excerpt:

In my case, the graphs I made looked just fine—it’s just that I didn’t understand how copy/pasting graphs between Excel and Word worked (at the time). This was in the mid-2000s, when memory wasn’t quite so plentiful, so many corporate email accounts had memory quotas. If you hit that quota, you would be locked out of your email account. You had to call IT and actually talk to a person! 

I was a lowly entry-level person at a financial services company and had done some Monte Carlo modeling involving 1,000,000 scenarios. We were developing a new mutual fund project, based on changing allocations over time as people moved towards retirement, and the company wanted me to model outcomes for different allocation trajectories.  After a “full” model run of one million scenarios, I made diagnostic graphs showing the distribution of key metrics (such as the annual accumulation of the fund, how many times the fund decreased while the owner was in retirement, and whether – and when – the money in the fund ran out)  so that we could analyze different potential fund strategies. The graphs themselves were fairly simple. 

Author(s): Mary Pat Campbell

Publication Date: 31 Aug 2022

Publication Site: Nightingale

Data visualization lessons: Jitter charts, screwups, and visionaries

Link: https://marypatcampbell.substack.com/p/data-visualization-lessons-jitter

Video:

Excerpt:

Jitter charts are my new favorite tool for displaying how distributions change over time.

I used them to great effect in my recent post One Bad Year? Comparing the Long-Term Public Pension Fund Returns Against Assumptions.

I’m often looking at distributions, and wanting to communicate something about how those distributions change over time, or how distributions compare. Often, I have to simply pick out key percentiles in those distributions, or key aspects, such as mean and standard deviation.

But why not graph all the points in one’s sample directly, if one has them?

That’s where jitter charts can help.

Author(s): Mary Pat Campbell

Publication Date: 16 Sep 2022

Publication Site: STUMP at substack

Python and Excel Working Together

Link: https://www.joveactuarial.com/blog/python-and-excel/

Graphic:

Excerpt:

When we’re exploring using data science tools for actuarial modelling, we’d often like to keep using existing Excel workbooks, which can contain valuable and trusted models and data.

Fortunately, using Python doesn’t mean abandoning Excel as there are some very powerful tools available that allow closely coupled interaction between the two.

These tools allow us to have the “best of both worlds” combining the ease of use of Excel and the power of Python.

We’re going to start with the simplest options and lead through to ways of building workflows that can contain both Excel workbooks and Python code.

With the range of tools available, it should be possible to have the ‘best of both worlds’: the familiarity of existing Excel workbooks and the power of the Python ecosystem working together.

Finally, if you’d like to learn  more, the author of XLWings, Felix Zumstein, has written an excellent book “Python for Excel“, which covers these topics in more detail. Highly recommended.

Author(s): Carl Dowthwaite

Publication Date: 1 Feb 2022

Publication Site: Jove Actuarial

THE FIRST ANNUAL CAS ACTUARIAL
TECHNOLOGY SURVEY

Link: https://www.casact.org/sites/default/files/2022-03/CAS-RP_First_Annual_CAS_Actuarial_Technology_Survey.pdf

Graphic:

Excerpt:

Excel continues to be actuaries’ most widely used software tool, with more than
94.3% of respondents reporting that they use it at least once a day.
• With that understood, most actuaries (92.3%) use more than one tool.
• Actuaries want to increase their proficiency in R (47.2%), Python (39.1%), SQL
(30.8%), and Excel (26.0%).
• No tool had more than 50% of respondents indicating that they wanted to increase
their proficiency.
• Time is the greatest barrier to learning new technology. (80.5% of respondents felt
so.)
• Newer analysis methods such as tree-based algorithms and artificial intelligence (AI)
are not widely used (16.5% and 7.0%, respectively).

Author(s): Casualty Actuarial Society

Publication Date: March 2022

Publication Site: CAS Research Paper

Police lose hacked therapy center criminal reports after spreadsheet error

Link:https://www.thebharatexpressnews.com/police-lose-hacked-therapy-center-criminal-reports-after-spreadsheet-error/

Excerpt:

The hack into the client database of the private Vastamo psychotherapy center was first exposed on October 21, 2020, when the patient data of tens of thousands of people was stolen and used to blackmail both l company and patients.

Investigators asked each victim to file a criminal complaint, and as of February 2021, more than 25,000 such reports had been submitted. The majority of complaints were lodged at the Pasila police station in Helsinki, but others were lodged elsewhere in the country.

….

Instead of a database, criminal reports were saved via Microsoft Excel files. Some of the files turned out to be unreadable when the police attempted to transfer them into the official system. The cause of the problem is unknown.

Detective Inspector Jari Illukka from the Helsinki Police Department told Svenska Yle that a dozen crime reports had disappeared from Excel, but the exact number is not known.

….

Police estimate that the records of more than 30,000 people were stolen during the Vastaamo data breach, and more than 22,000 of those victims have since reported the crime.

However, a little more than three thousand declaration forms had been given to the police at the end of January, that is to say one victim in ten.

Publication Date: 7 Feb 2022

Publication Site: Bharat Express News

WordXLe: Wordle in Excel

Link:https://sysmod.wordpress.com/2022/01/25/wordxle-wordle-in-excel/

Excerpt:

If you play Wordle daily, or the French version LeMot, you might want to practice more often. For fun, I created an Excel version that you can download, WordXLe. It has sheets for both English and French versions. The dictionaries are:


English main (for validation) c.12000 words from the SOWPODS dictionary; for play c.1100 words.


Version française: principal c.8000 mots; https://github.com/hbenbel/French-Dictionary/blob/master/dictionary/dictionary.txt

Le jeu 1700 mots. https://www.freelang.com/dictionnaire/dic-francais.php
I removed all accents to simplify the game.


It uses Conditional Formatting for colouring, Data Validation to enforce some letter entry rules, no VBA macros, just formulas. The sheets are protected, but it’s easy to unhide things in Excel if you really want to so I’ll leave that as a challenge. 

Author(s): Patrick O’Beirne

Publication Date: 25 Jan 2022

Publication Site: sysmod

Microsoft Excel: The Program’s Designer Reveals The Secrets Behind The Software That Changed the World 25 Years Ago

Link:https://www.thedailybeast.com/microsoft-excel-the-programs-designer-reveals-the-secrets-behind-the-software-that-changed-the-world-25-years-ago

Excerpt:

In a year when big names from the digital realm profoundly affected the world—Mark Zuckerberg or Julian Assange, take your pick—it’s appropriate to add one more: Douglas Klunder. While largely unnoticed, 2010 marked the 25th anniversary of perhaps the most revolutionary software program ever, Microsoft Excel, and Klunder, now an unassuming attorney and privacy activist for the American Civil Liberties Union in Washington state, gave it to us.

…..

For Doug Klunder, the mission 25 years ago wasn’t so grandiose. As lead developer of Excel, he was handed the job of vaulting Microsoft—then known best for MS-DOS, the operating system in IBM’s PCs—to the forefront in business applications. “We decided it was time to do a new, better spreadsheet,” recalls Klunder, now 50, who joined Microsoft straight out of MIT in 1981 (part of the interview process included lunch with Bill Gates and Steve Ballmer at a Shakey’s pizza parlor).

…..

Klunder and his team came up with “intelligent recalc,” an approach where the program updated only the cells affected by the data change rather than all the formulas in the spreadsheet. Klunder credits Gates with the idea for how to implement the feature—though he says Gates eventually told him he hadn’t implemented what he had in mind at all. Klunder thinks Gates misremembered the discussion, but adds, “Maybe he actually did have a more brilliant idea that now is lost forever.”

Author(s):Thomas E. Weber

Publication Date:14 July 2017 (originally published 2010)

Publication Site: Daily Beast

Top Excel experts will battle it out in an esports-like competition this weekend

Link:https://www.pcworld.com/article/559001/the-future-of-esports-is-microsoft-excel-and-its-on-espn.html

Graphic:

Excerpt:

Move over, League of Legends. Does anyone even care about Overwatch? No, the real future of esports is spreadsheets and Microsoft Excel. Don’t believe us? Then tune in to ESPN3 or YouTube this weekend to find out.

No, this isn’t a joke. The Financial Modeling World Cup will be held this weekend entirely in Microsoft Excel. And the finals (the quarterfinals, semifinals, and the final match) will all be broadcast live as they happen at 9 AM PT. Everyone’s playing for a total prize of $10,000 — funded by Microsoft, of course.

Author(s):Mark Hachman

Publication Date: 10 Dec 2021

Publication Site: PC World

Autocorrect errors in Excel still creating genomics headache

Link: https://www.nature.com/articles/d41586-021-02211-4

Graphic:

Excerpt:

In 2016, Mark Ziemann and his colleagues at the Baker IDI Heart and Diabetes Institute in Melbourne, Australia, quantified the problem. They found that one-fifth of papers in top genomics journals contained gene-name conversion errors in Excel spreadsheets published as supplementary data2. These data sets are frequently accessed and used by other geneticists, so errors can perpetuate and distort further analyses.

However, despite the issue being brought to the attention of researchers — and steps being taken to fix it — the problem is still rife, according to an updated and larger analysis led by Ziemann, now at Deakin University in Geelong, Australia3. His team found that almost one-third of more than 11,000 articles with supplementary Excel gene lists published between 2014 and 2020 contained gene-name errors (see ‘A growing problem’).

Simple checks can detect autocorrect errors, says Ziemann, who researches computational reproducibility in genetics. But without those checks, the errors can easily go unnoticed because of the volume of data in spreadsheets.

Author(s): Dyani Lewis

Publication Date: 13 August 2021

Publication Site: nature

Have Fun With Approximations!

Link: https://www.linkedin.com/pulse/have-fun-approximations-mary-pat-campbell/

Graphic:

Pdf: https://drive.google.com/file/d/0ByabEDuWaN6FNmZhTDBYeEVrNVE/view?resourcekey=0-U4GI2_9zn4UQdWza1bq95w

Excerpt:

In the pre-computer days, people used these approximations due to having to do all calculations by hand or with the help of tables. Of course, many approximations are done by computers themselves — the way computers calculate functions such as sine() and exp() involves approaches like Taylor series expansions.

The specific approximation techniques I try (1 “exact” and 6 different approximation… including the final ones where I put approximations within approximations just because I can) are not important. But the concept that you should know how to try out and test approximation approaches in case you need them is important for those doing numerical computing.

Author(s): Mary Pat Campbell

Publication Date: 3 February 2016 (updated for links 2021)

Publication Site: LinkedIn, CompAct, Society of Actuaries

Cell Maps – Critical Comments

Link: https://logiccomppeople.blogspot.com/2021/04/cell-maps-critical-comments.html

Graphic:

Excerpt:

Cell maps are intended as tools for reviewing spreadsheets. If you spot an error or an inconsistency in the cell map this should be recorded and, if practicable, corrected.

The cell mapping software provides a method for recording a reviewer’s comments. All comments are linked to a specific map (or data table), The comments for a workbook under review are collated in a single worksheet.

Publication Date: 18 April 2021

Publication Site: Logic, Computing, and People