• wtf (unregistered)

    The real WTF is Access....

  • tragomaskhalos (unregistered)
    At some point, however, she must have messed up and the spreadsheet ended up in a weird state, with formulas referring to cells in other rows, or sometimes even referring to nothing, creating a whole bunch of inconsistent values.
    *Terrifyingly* easy to do in Excel, which makes using it for anything as important as this a massive wtf.
  • HR (unregistered)

    The real WTF is in the numbers.

  • Cep (unregistered) in reply to wtf

    I agree!

    Surely though this discrepancy in the figures would have been picked up during testing though? After all wouldn't you port over the existing data to check it matches first?

  • (cs) in reply to wtf
    wtf:
    The real WTF is Access....

    Access is used as the front end here, talking to a SQL back-end. That's not like the typical WTF uses that we usually find here, where the Access database is used as the central repository.

  • (cs)
    At some point, however, she must have messed up and the spreadsheet ended up in a weird state, with formulas referring to cells in other rows, or sometimes even referring to nothing, creating a whole bunch of inconsistent values.
    And thus the financial crisis was born.
  • An apprentice (unregistered)

    Just as expected from a financial company. The one I used to work at implemented everything in Excel - using it for data storage, business logic calculations and also as reporting engine. Only from time to time did we have to copy/paste a csv file brought on a usb stick from somewhere else.

  • Zomby (unregistered) in reply to Triscopic
    Triscopic:
    At some point, however, she must have messed up and the spreadsheet ended up in a weird state, with formulas referring to cells in other rows, or sometimes even referring to nothing, creating a whole bunch of inconsistent values.
    And thus the financial crisis was born.

    Really this is how finance actually works. The guy who cost a French bank 700mil did it by using his knowledge of VBA to mess with their spreadsheets. See also Barclays buying Lehman Brothers assets they didn't intend too.

    Excel is a cancer and must die.

  • (cs)

    I'm not surprised at all. I work on a 401(k) website, and when we had to modify our withdrawals module to handle Roth 401(k) plans, we uncovered all sorts of existing bugs that had been in production for years. Or, to be more precise, the testing team uncovered all these existing errors during their regression testing. It looked like we broke the code; we had to prove that these bugs existed all along.

  • Gnubeutel (unregistered)

    I don't see any WTFs. That's exactly how banks work.

    They use Excel (including buggy Excel implementations of the f-test), make a fuzz about wrong numbers when in fact the numbers are of little importance because analysts/traders do what they want anyway.

    And i know what i'm taking about. We deal with a lot of banks and i implemented our companies several different yield methods (that beat FinancialCad by a mile).

  • blindman (unregistered)

    Not unusual at all. I had a job at a telecommunications firm about 10 years which involved a lot of transferring excel-hell applications to a database (yes, Access, and it worked very well for our department thank you). At first I would panic every time a manager would tell me the numbers in my database were different than the numbers on their spreadsheets. But after a while I realized that nine times our of ten, or even ninety nine times out of one hundred, it was their spreadsheet that was in error and they had been working with bad numbers for years. No WTF here. This is just normal.

  • ShatteredArm (unregistered)

    What's so hard about Yield? Isn't it just the dividend divided by the share price?

  • (cs) in reply to Gnubeutel
    Gnubeutel:
    I don't see any WTFs. That's exactly how banks work.

    They use Excel (including buggy Excel implementations of the f-test), make a fuzz about wrong numbers when in fact the numbers are of little importance because analysts/traders do what they want anyway.

    And i know what i'm taking about. We deal with a lot of banks and i implemented our companies several different yield methods (that beat FinancialCad by a mile).

    TRWTF is this isn't TRWTF.

  • (cs) in reply to ShatteredArm
    ShatteredArm:
    What's so hard about Yield? Isn't it just the dividend divided by the share price?
    Shhh! Stop giving away corporate secrets!
  • Ben4jammin (unregistered) in reply to ShatteredArm
    ShatteredArm:
    What's so hard about Yield? Isn't it just the dividend divided by the share price?

    Apparently some value is lost once it is run through the Helen Random Copy/Paste Algorithm or HRCPA for short.

  • Not Bitter At All (unregistered)

    That's the recurring WTF: your carefully crafted transition from legacy to new code exposes ancient bugs lovingly preserved, because every busybody's attention is heightened to excruciating sensitivity. And the bugs are your fault, even if you prove otherwise.

    The concurrent WTF is the lack of parallel ops or phased transition. "Throw the switch Igor! MWAHAHAHA"

  • imu (unregistered)

    I use Excel to print out graph paper grids sometimes.

    If Excel 2007 had the hidden flight simulator, then I'd have a second reason to use it.

  • Graeme (unregistered) in reply to ShatteredArm

    There are other yield numbers for bonds. Flat yield is the same as for shares, yield to maturity, yield to call (for callable bonds), yield to worst etc.

  • Mikoangelo (unregistered)

    The Real WTF is that Mark Bowytz apparently thinks that “whom” is just a fancy way of saying “who.”

    From Mark - good catch (or trolling?). Really, I should have used "who" because the rule goes if you can substitute he/she/they for the subject, go with who. Him/her/them, gets "whom". Customers invested their money --> They invested their money --> customers who invested money. Also, Google says I'm wrong.

  • Graeme (unregistered) in reply to An apprentice
    An apprentice:
    Just as expected from a financial company. The one I used to work at implemented everything in Excel - using it for data storage, business logic calculations and also as reporting engine.
    A place I used to work for did exactly the same as this WTF, except that it was never printed. It was for internal use.

    It was about as impractical as you can get. Only one analyst could edit it at a time. It had to be manually copied to servers at other sites, it took ages to run a macro to update everything with data from Bloomberg and IBES.

  • (cs)

    This reminds me of Isaac Asimov's The Machine that Won the War (read it here).

    Addendum (2008-12-02 12:27): Seriously, read it! It's an excellent story, rife with delicious WTF material, and it's only four pages long.

  • Jay (unregistered)

    I think most of you know this massive excel goofup...

    http://www.theregister.co.uk/2004/07/16/excel_vanishing_dna/

  • Anonymous (unregistered)

    Maxim is an awesome name. You should be a stuntman or something - programming is way below anyone with a name as cool as that.

  • BJ Upton (unregistered) in reply to JamesQMurphy
    JamesQMurphy:
    wtf:
    The real WTF is Access....

    Access is used as the front end here, talking to a SQL back-end. That's not like the typical WTF uses that we usually find here, where the Access database is used as the central repository.

    And, in all honesty, this is usually a pretty robust solution.

    Our brain-dead IT department is having a hard time wrapping their heads around it, though.

  • Vic Tim (unregistered) in reply to Anonymous
    Anonymous:
    Maxim is an awesome name. You should be a stuntman or something - programming is way below anyone with a name as cool as that.

    Immediately before opening this site I was reading a Maxim IC datasheet. It is a cool name...

    Captcha: haero (again! with AutoComplete!)

  • Viktor (unregistered) in reply to Andy Goth

    I was thinking exactly the same thing!

  • Viktor (unregistered) in reply to Viktor
    Viktor:
    I was thinking exactly the same thing!

    (and by that, I meant that the story reminds me of Asimov's story The Machine That Won The War

  • (cs)

    Honestly, I half expected the Yield column to contain the formula (with variable names rather than column references for sanity's sake)

    = SUM 
    (
        (Abs(LastWeekValue) / Log(TwoWeekAgoValue))
       *
         (1 - DateDiff(LastWeek, Now, "w"))
       + 
         ((1 + RAND()) * 100)
    )
    

    Given how full of Bank Stuff this company seems to be, the formula would work just as well as anything they could come up with.

  • Ben (unregistered) in reply to JamesQMurphy
    JamesQMurphy:
    wtf:
    The real WTF is Access....

    Access is used as the front end here, talking to a SQL back-end. That's not like the typical WTF uses that we usually find here, where the Access database is used as the central repository.

    agreed - Access makes a reasonable GUI for simple, small user-base apps.

    Having a sensible database behind it means if they need a more in vogue 'enterprisey' (infinitely configurable 57 tier, requiring a 57 strong team of consultants to spend years building, and years more debugging) system they can always swap one in.

  • (cs) in reply to Andy Goth
    Andy Goth:
    This reminds me of Isaac Asimov's The Machine that Won the War (read it here).

    Addendum (2008-12-02 12:27): Seriously, read it! It's an excellent story, rife with delicious WTF material, and it's only four pages long.

    Agreed agreed agreed!

  • (cs)

    It seems that Financial analysts like to go with their gut alot. I used to work for the IT staff of a collection agency. Even though the system would be able to calculate the collector monthly quotas based on past trends, etc, the final numbers would go through what we called the <ManagersName> Process, where that manager would go through the final list of collection accounts, collectors and their quotas and fudge them based on intangibles like "I know so-and-so could do this amount". It seemed rather brash, but I guess that's why I'm not a financial analyst.

  • (cs)

    Also:

    After having to awkwardly sit through the rest of the meeting where "Plan B" (resurrect Helen) was discussed,

    So wait a second-- instead of just replacing Helen, they KILLED HER? And they can bring her back?

    Yeah, I'd be sitting awkwardly too if I did IT for Wolfram & Hart.

  • (cs)

    I work in BI in a financial company too, and about a year ago, we had a new IT director who decided that what I did was all smoke and mirrors and 'too complicated' with all that relational stuff. So behind my back, he used his SQL skills set up some alternative reporting.

    The core of this was the 'Daily Extract' which managed to include just about every no-no known to SQL. (Why use lookup tables when you can simply use a big enough CASE statement? Use those fancypants 'Integers' for Primary Keys? Why bother when there's a perfectly good 'numerical' data type?) Not only was it full of bad practice it was full of bad logic, and produced data that was just wrong.

    This ran on the live database and would bring down the live web server, the call-centre application and a few automated systems. Despite only extracting yesterday's data, it ran at 9am and took about 15 minutes.

    Eventually he left and people started coming to me with Daily Extract problems. However when I rewrote the job and took the errors out they almost all complained it had screwed up their graphs and I got a call from the big scary boss himself telling me to 'put it back like it was'. It now runs in a few seconds soon after midnight and no longer brings down the whole database, but the information is still wrong, but it's the right wrong, not the wrong wrong.

  • Dirk Diggler (unregistered) in reply to Vic Tim
    Vic Tim:
    Anonymous:
    Maxim is an awesome name. You should be a stuntman or something - programming is way below anyone with a name as cool as that.

    Immediately before opening this site I was reading a Maxim IC datasheet. It is a cool name...

    Captcha: haero (again! with AutoComplete!)

    Me too! I was looking at an obsolete chip the MAX038.

  • (cs)
    A bad recommendation could make or break the financial managers

    If a bad recommendation could "make" the financial managers, wouldn't that make it a good recommendation?

  • (cs) in reply to halcyon1234
    halcyon1234:
    Yeah, I'd be sitting awkwardly too if I did IT for Wolfram & Hart.

    Imagine trying to network all of those branch offices together.

  • (cs)

    Sounds like the Clbuttic ASP application I have to maintain... the current reports are wrong and my new reports are right, but try explaining that to users who just say "It doesn't match the old version"

  • Mike (unregistered) in reply to blindman
    blindman:
    No WTF here. This is just normal.

    ... which is the real WTF.

  • (cs) in reply to ChrisSamsDad
    ChrisSamsDad:
    I work in BI in a financial company too, and about a year ago, we had a new IT director who decided that what I did was all smoke and mirrors and 'too complicated' with all that relational stuff. So behind my back, he used his SQL skills set up some alternative reporting.<snip> It now runs in a few seconds soon after midnight and no longer brings down the whole database, but the information is still wrong, but it's the right wrong, not the wrong wrong.
    TRWTF is that this isn't a front page post.
  • Bruce W (unregistered)

    At my last job I helped bring in revolutionary ideas like "spreadsheets" instead of ledger paper. My first January there the payroll manager asked me to help calculate vacation hours. She had been calculating vacation by hand using her trusty ten-key and ledger paper. I put together a spreadsheet where she could enter the hours and a macro calculated the vacation. (It had to be a macro because the rules for calculating and awarding vacation were nutty.)

    When she first used the spreadsheet she came to me and said, "The computer is wrong." I reviewed the formula with her and it looked fine. Then she pulled out her ten-key (I wanted to throw that thing into a trash compactor). She went through her formula which did five multiplication and division steps. She pointed at the display and said, "Look, the results are different." I watched her do it again and noticed at one point in the formula the display read 0.00. I told her to stop and asked her to move the decimal setting. Low and behold there were more numbers beyond two decimal places.... She quickly realized she had been incorrectly calculating vacation for 10 years. Also, since her magic formula was all multiplication and division the five steps could be reduced to multiplying the total hours worked by 0.08.

  • Ollie Jones (unregistered)

    Excel does have an Audit toolbar, which scribbles little yellow lines showing dependencies between cells. It's real handy for double-checking cut n paste.

    Y'd think a bank might have an auditor on staff. Or at least somebody who'd read through Excel for Dummies. Especially as their business's integrity depended on this one Very Large Spreadsheet.

    A poor worker blames her tools. This has been true since the Stone Age.

  • Jamie (unregistered) in reply to wtf
    wtf:
    The real WTF is Access....

    Oh come on, put some effort into it.

  • (cs) in reply to Gnubeutel
    Gnubeutel:
    And i know what i'm taking about. We deal with a lot of banks and i implemented our companies several different yield methods (that beat FinancialCad by a mile).
    Care to elaborate on that? I would be interested in knowing which yield calculations were a problem and what the actual problem was.

    Thx

  • Glow-in-the-dark (unregistered)

    I think this was by Ralf Hildebrandt, but he may have just repeated it (I ended up with an attribution this way).

    The quote, true as ever: "I work for an investment bank. I have dealt with code written by stock exchanges. I have seen how the computer systems that store your money are run.

    If I ever make a fortune, I will store it in gold bullion under my bed."

  • Duke of New York (unregistered)

    TRWTF is that Maxim told this story to some out of the way programmer blog and not to, say, the Wall Street Journal.

  • (cs)

    Hah. I had a similar issue with an Access database that did life insurance quotes. I changed a bunch of stuff and sent it back, and they lost their minds because the report numbers for people with insurance over 250k didn't match the quotes from the front end.

    So I dug into the front end to find that the actuarial multiplier for quotes over 250k had been left out of the equation, and they'd been billing everyone wrong for literally years.

    This stuff happens all the time. I just fixed a piece of ancient COBOL which has probably been spitting out crap for two decades...Spitting it out to the point where the procedure for manually correcting the inevitable inbalance is documented with more thoroughness than the procedure for closing the financial year.

  • ShatteredArm (unregistered) in reply to Graeme
    Graeme:
    There are other yield numbers for bonds. Flat yield is the same as for shares, yield to maturity, yield to call (for callable bonds), yield to worst etc.

    Well, you'd think if they have a single "yield" column, there'd really just be two cases... One for debt, and one for equity.

    Sure, YTM would be slightly more complicated, but still far from voodoo math.

  • lemming (unregistered)

    Remembers me to the great Fudge Factory.

  • (cs) in reply to JamesQMurphy
    JamesQMurphy:
    wtf:
    The real WTF is Access....

    Access is used as the front end here, talking to a SQL back-end. That's not like the typical WTF uses that we usually find here, where the Access database is used as the central repository.

    Actually this is still a HUGE WTF because Access will open an exclusive lock to any table that it connects to. Access is really only meant for single desktop use and really doesn't support any more than one user at all really. So despite having an eterprise class RDMS like SQL Server its all for naught when that dreaded plague known as Access steps into the picture.

  • Dirk Diggler (unregistered) in reply to cod3_complete
    cod3_complete:
    'Actually this is still a HUGE WTF because Access will open an exclusive lock to any table that it connects to'.
    I think you are incorrect. If this was a troll carry on.

Leave a comment on “The Great Excel Spreadsheet”

Log In or post as a guest

Replying to comment #232381:

« Return to Article