Yeah, very usual, and these companies make me very happy - they make exceptionally well paying customers when they critically need to redo everything into a "normal" application.
I did this when I worked at JPM Chase in 2008; not as developer but as an analyst on treasury side. I executed $B daily of margin trades through an Excel macro.
Not very sophisticated. Each row in the spreadsheet was a trade. The loop would just send commands to a 1970s-era mainframe terminal application. Everything had to be executed in order, just like a human using the keyboard to enter the trades manually. The trades required several calculations, and you'd have to enter the results in terminal screen to execute the trade. Other analysts had been doing those calculations one by one on a calculator next to their mouse. The efficiency being, they didn't need to switch between apps on their PC.
This macro only existed because I built it. They didn't like it much, they wanted me to do it manually. I said OK and kept using the macro anyway. I quit after ~6 months out of boredom.
Unreal. I was working as a developer in their investment bank at that time and by then a lot of their software suite was pretty mature. In credit risk (my group) they had built a sophisticated set of applications to prevent being burned by another Enron. I'm pretty surprised they couldn't ask someone to take your macro and replicate it into a web app, but of course no one asks for our opinion. :)
Sure - but I am guessing a few errors here and there would have much less impact than a single programming mistake. Not that I think this is the right way, but I do understand where they come from.
Human error was much more common. But they did have another tool for reconciling trades that was better. It was web based and auto reconciled most things that were obvious matches. I don't recall why, but they were not too concerned with mistakes so long as we could fix them by the end of business. I think it was because that's when clients would see something was off on their statement/account. There was never a concern of losing money. I wish I could remember why, but this is my cumulative experience in banking industry and I quickly forgot everything I learned.
I can believe it. Once upon a time, a friend of mine at one of the largest oil companies told me how they were passing around a complex spreadsheet with tons of tabs and formulas and it was for managing the budgets of all their chemical plants around the world. I tried to explain how I could just put it all into a database and whip up a front end for it, but he said there's no way a consultant like me could get anywhere near his department since all firms had to be vetted extensively, there'd be a big RFP, etc, and anyway it sounds like they thought Excel was just fine for managing all this money.
I was encouraged to use Excel/VBA by our official Auditors and Advisors at a Fortune 500 public company. The rationalization was (whatever you might make of this):
- Excel is generally "contained" without too many external dependencies (not really true, but truer than others...)
- Excel is easily auditable (true for excel formulas, less so for VBA, but sure, better than Perl etc)
- Excel is easily versioned and frozen (some truth to this)
Obviously none of these answers are universally correct, but there was some truth to this. And Auditors/Advisors are generally more concerned about risk mitigation than other goals.
I don't know about billions, but I've personally witnessed an Excel spreadsheet for millions of dollars that was manually updated.
It was sent over an encrypted Symantec thingie (where it was processed on my side), but errors weren't uncommon, it was really hard to automate, and I did get to see people being over-(and under-)charged due to manual errors.
Edit: a sibling comment has a story about moving billions over a macro, not mere millions :) .
One place I worked, they processed and paid payroll twice one month because someone dropped the files again and the ESB dutifully picked them up and sent them. That was a $6m mistake, took them years to work out how to reclaim the money from the workers (unions involved, contract lawyers, you name it).
The admins didn't allow my team too much software, especially no compiler or something, but(!) they allowed MS Office.
So what happened was, that we started to program everything we need in VBA, with Word, Excel or Access as base, depending on the needs.