Tag: Excel spreadsheets

[109] Data Falsificada (Part 1): “Clusterfake”

Mary Pat Campbell

Link: https://datacolada.org/109

Graphic:

Excerpt:

Two summers ago, we published a post (Colada 98: .htm) about a study reported within a famous article on dishonesty (.htm). That study was a field experiment conducted at an auto insurance company (The Hartford). It was supervised by Dan Ariely, and it contains data that were fabricated. We don’t know for sure who fabricated those data, but we know for sure that none of Ariely’s co-authors – Shu, Gino, Mazar, or Bazerman – did it [1]. The paper has since been retracted (.htm).

That auto insurance field experiment was Study 3 in the paper.

It turns out that Study 1’s data were also tampered with…but by a different person.

That’s right:
Two different people independently faked data for two different studies in a paper about dishonesty.

The paper’s three studies allegedly show that people are less likely to act dishonestly when they sign an honesty pledge at the top of a form rather than at the bottom of a form. Study 1 was run at the University of North Carolina (UNC) in 2010. Gino, who was a professor at UNC prior to joining Harvard in 2010, was the only author involved in the data collection and analysis of Study 1 [2].

Author(s): Uri Simonsohn, Leif Nelson, and Joseph Simmons

Publication Date: 17 Jun 2023

Publication Site: Data Colada

Thinking Spreadsheet

Mary Pat Campbell

Link: https://joelgrus.github.io/thinking-spreadsheet/

Graphic:

Excerpt:

There are many books about spreadsheets out there. Most of these books will tell you things like “How to save a file” and “How to make a graph” and “How to compute the present value of a stream of cashflows” and “How to use conjoint analysis to figure out which features you should add to the next version of your company’s widgets in order to impress senior management and get a promotion and receive a pay raise so you can purchase a bigger boat than your neighbor has.”

This book isn’t about any of those. Instead, it’s about how to Think Spreadsheet. What does that mean? Well, spreadsheets lend themselves well to solving specific types of problems in specific types of ways. They lend themselves poorly to solving other specific types of problems in other specific types of ways.

Thinking Spreadsheet entails the following:

  • Understanding how spreadsheets work, what they do well, and what they don’t do well.
  • Using the spreadsheet’s structure to intelligently organize your data.
  • Solving problems using techniques that take advantage of the spreadsheet’s strengths.
  • Building spreadsheets that are easy to understand and difficult to break.

To help you learn how to Think Spreadsheet, I’ve collected a variety of curious and often whimsical examples. Some represent problems you are likely to encounter out in the wild, others problems you’ll never encounter outside of this book. Many of them we’ll solve multiple times. That’s because in each case, the means are more interesting than the ends. You’ll never (I hope) use a spreadsheet to compute all the prime numbers less than 100. But you’ll often (I hope) find useful the techniques we’ll use to compute those prime numbers, and if you’re clever you’ll go away and apply them to all sorts of real-world problems. As with most books of this sort, you’ll really learn the most if you recreate the examples yourself and play around with them, and I strongly encourage you to do so.

Author(s): Joel Grus

Publication Date: originally in dead-tree form 2010, accessed 29 Oct 2022

Publication Site: Joel Grus github

Architect of Allianz fraud made $60 mln as he lied to investors, U.S. says

Mary Pat Campbell

Link: https://www.reuters.com/business/finance/architect-allianz-fraud-made-60-mln-he-lied-investors-us-says-2022-05-17/

Excerpt:

The star portfolio manager at the centre of a fraud at the U.S. funds unit of Allianz SE (ALVG.DE) relied on the German insurer’s good name to lure investors and thrived from a lack of oversight as he pocketed $60 million in pay, U.S. authorities said.

Gregoire “Greg” Tournant, a citizen of France and the United States, was indicted on Tuesday for securities fraud, investment adviser fraud, wire fraud and obstruction of justice in a scheme that ran from 2014 to 2020. read more

It was a major development in a two-year saga that has haunted and embarrassed Allianz, one of the globe’s biggest financial firms, and began after the $11 billion in funds managed by Tournant collapsed as markets roiled with the outbreak of the coronavirus in early 2020.

U.S. prosecutors on Tuesday said Tournant faked documents, fabricated risk reports, altered spreadsheets, and lied about the investment strategy.

Author(s): Tom Sims, Alexander Hübner and John O’Donnell

Publication Date: 18 May 2022

Publication Site: Reuters

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

Mary Pat Campbell

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

Excel autocorrect errors still plague genetic research

Mary Pat Campbell

Link: https://cosmosmagazine.com/science/biology/excel-autocorrect-errors-still-plague-genetic-research/

Graphic:

Excerpt:

Earlier this year we repeated our analysis. This time we expanded it to cover a wider selection of open access journals, anticipating researchers and journals would be taking steps to prevent such errors appearing in their supplementary data files.

We were shocked to find in the period 2014 to 2020 that 3,436 articles, around 31% of our sample, contained gene name errors. It seems the problem has not gone away, and is actually getting worse.

Author(s): Mark Ziemann, Deakin University and Mandhri Abeysooriya, Deakin University

Publication Date: 27 August 2021

Publication Site: Cosmos magazine

Intro to Financial Modelling – Part 19: Wrap-up

Mary Pat Campbell

Link: https://www.icaew.com/technical/technology/excel/excel-community/excel-community-articles/2021/intro-to-financial-modelling-part-19

Graphic:

Excerpt:

There has been significant disruption in how organisations conduct business and the way we work over the past year and a half. However, financial modellers and developers have had to continue to build, refine and test their models throughout these unprecedented times. Figure 1 below summarises the areas we have covered in the blog series and how they fit together to form the practical guidance of how to follow and implement the Financial Modelling Code.

Author(s): Andrew Paw

Publication Date: 19 August 2021

Publication Site: ICAEW

Virtual Meetup: To Err is Human but to ISERR is Never OK!

Mary Pat Campbell

Video description:

Have you ever built a perfect financial model without any errors? Thought not! And for that reason, all good modellers know they need to include some error checks. But what is not as clear is how many error checks you should have, when you should include them and what form they should take. Excel “helpfully” provided us with functions like ISERR, ISERROR and IFERROR but as you progress your modelling journey you should learn to avoid these functions. Plus, you also learn the sad truth that Excel can’t even do basic maths sometimes! Join us to hear from financial modelling specialist Andrew Berg, who has spent years building models, and so happily admits he has probably already made most of the mistakes you haven’t yet had a chance to! The good news is that he is willing to share the tips he has learned about the right types of error checks to add to your models so you don’t have to learn the hard way. ★Download the resources here ► https://plumsolutions.com.au/virtual-… ★Register for more meetups like this ► https://plumsolutions.com.au/meetup/ ★Connect with Andrew on Linkedin ► https://www.linkedin.com/in/andrew-be…

Author(s): Andrew Berg, Danielle Stein Fairhurst

Publication Date: 2 June 2021

Publication Site: YouTube

How the government’s mistaken prices disclosure derailed a big follow-on solicitation

Mary Pat Campbell

Link: https://federalnewsnetwork.com/contracting/2021/07/how-the-governments-mistaken-prices-disclosure-derailed-a-big-follow-on-solicitation/

Excerpt:

When the Defense Information Systems Agency sought a new satellite services acquisition on behalf of the Navy, it included a spreadsheet so bidders could fill in their prices. But the spreadsheet included the prices from the current contract, which were supposed to be inaccessible. For how things turned out, Smith Pachter McWhorter procurement attorney Joe Petrillo joined Federal Drive with Tom Temin.

…..

Joe Petrillo: Sure. This is another excel spreadsheet disaster, and we talked about one a few weeks ago. It involved an acquisition of satellite telecom services for the Navy’s Military Sealift Command. It was an acquisition of commercial satellite telecommunications services. And they were divided into both bandwidth and non-bandwidth services. And the contract would be able to run to for up to 10 years in duration. Part of the contract, as you said, was an excel spreadsheet of the various different line items with blanks for offers to include their price. Unfortunately, this spreadsheet had hidden tabs, 19 hidden tabs, and those included, among other things, historical pricing information from the current contract. So Inmarsat, which was the incumbent contractor, holding that contract, notified the government and said, look you’ve disclosed our pricing information, do something about it. So the government deleted the offending spreadsheet from the SAM.gov website. But they understood and this was the case, third party aggregators had already downloaded it, and it was out there, it was available.

Author(s): Tom Temin, Joe Petrillo

Publication Date: 8 July 2021

Publication Site: Federal News Network

Several Ways to Improve Your Use of Excel for Actuarial Production

Mary Pat Campbell

Link: https://www.soa.org/sections/small-insurance/small-insurance-newsletter/2021/june/stn-2021-06-mathys/

Graphic:

Excerpt:

Create a Consistent Structure for Calculations

When spreadsheets are created ad-hoc, the usage of time steps tends to be inconsistent: advancing by rows in one sheet, columns in another, and even a mix of the two in the same sheet. Sometimes steps will be weeks, other times months, quarters, or years. This is confusing for users and reviewers, leads to low trust, increases the time for updates and audits, and adds to the risks of the spreadsheet.

A better way is to make all calculations follow a consistent layout, either across rows or columns, and use that layout for all calculations, regardless if it requires a few more rows or columns. For example, one way to make calculations consistent is with time steps going across the columns and each individual calculation going down the rows:

Author(s): Stephan Mathys

Publication Date: June 2021

Publication Site: Small Talk at the Society of Actuaries

The tyranny of spreadsheets

Mary Pat Campbell

Link: https://financialpost.com/fp-work/the-tyranny-of-spreadsheets-we-take-numbers-for-granted-until-we-run-out-of-them

Excerpt:

Somewhere in PHE’s data pipeline, someone had used the wrong Excel file format, XLS rather than the more recent XLSX. And XLS spreadsheets simply don’t have that many rows: 2 to the power of 16, about 64,000. This meant that during some automated process, cases had vanished off the bottom of the spreadsheet, and nobody had noticed.

The idea of simply running out of space to put the numbers was darkly amusing. A few weeks after the data-loss scandal, I found myself able to ask Bill Gates himself about what had happened. Gates no longer runs Microsoft, and I was interviewing him about vaccines for a BBC program called How to Vaccinate The World. But the opportunity to have a bit of fun quizzing him about XLS and XLSX was too good to pass up.

I expressed the question in the nerdiest way possible, and Gates’s response was so strait-laced I had to smile: “I guess… they overran the 64,000 limit, which is not there in the new format, so…” Well, indeed. Gates then added, “It’s good to have people double-check things, and I’m sorry that happened.”

Exactly how the outdated XLS format came to be used is unclear. PHE sent me an explanation, but it was rather vague. I didn’t understand it, so I showed it to some members of Eusprig, the European Spreadsheet Risks Group. They spend their lives analyzing what happens when spreadsheets go rogue. They’re my kind of people. But they didn’t understand what PHE had told me, either. It was all a little light on detail.

Author(s): Tim Harford

Publication Date: 29 June 2021

Publication Site: Financial Post

Huge vaccine error: 9,000 patients were forgotten

Mary Pat Campbell

Link: https://stiften.dk/artikel/k%C3%A6mpe-vaccinefejl-9-000-patienter-blev-glemt

Excerpt (via Google Translate):

Jørgen Schøler Kristensen, Medical Director at Aarhus University Hospital, explains the error as follows:

– When patients with a civil registration number beginning with 0 had to be entered, the civil registration number was declared invalid if a hyphen was missing. So 26 percent of the civil registration numbers we have reported have not been entered correctly, so they have not received the invitation in their e-box to be vaccinated as they should have had, says Jørgen Schøler Kristensen.

Patients born the first nine days of any month and having been unlucky with a hyphen were forgotten.

Additional:

Author(s): Jens Christian Thaysen

Publication Date: 19 March 2021

Publication Site: Århus Stiftstidende

The Biggest Business Fails of All Time

Mary Pat Campbell

Link: https://moneywise.com/a/the-biggest-business-fails-of-all-time

Excerpt:

3. Spreadsheet error costs JPMorgan $3.1 billion

You might recall the “London Whale” incident in 2012, when notorious trader Bruno Iksil — whose other monikers include the White Whale and even Voldemort — conducted a series of credit default swaps that cost JPMorgan Chase $6.2 billion.

What you might not know, however, is that half the loss was incurred by a simple Excel spreadsheet error.

Bloomberg reports that the Excel model, which relied heavily on copy and pasting of information, accidentally “underestimated risk by half.”

Author(s): Serah Louis

Publication Date: 1 March 2021

Publication Site: MoneyWise