Live data from Hacker News

Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

wsj.com

191–200 of 317 posts

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#191
post #184
post #142

Earlier quoted context omitted.

At a former job I had to build a reporting system. And had the trust to build it however I wanted. But it was my job to make it sufficiently useful that everyone would use it. The smartest thing that I did was make it accessible from Excel. You could build a spreadsheet off of my report. Refresh the spreadsheet, the report ran, you got updated data. I got essentially 100% adoption, and the rest of my job was spent fi…

This sounds an incredibly useful capability to have. May I ask which data connector did you used to pull data, and how did you made it available in that format from the app side?

Excel has a feature for pulling data stored in HTML tables into a sheet called "Web Queries"[0]. It also has a feature for automatically building these in the form of .iqy files. When I worked at Abbott Labs, it was a big deal to be able to offer export-to-Excel in a way that Excel could refresh automatically (or manually). This made it a breeze since we could register an .iqy serializer for a dataset and just give a download link to it in place of a CSV, JSON, or XML file.

[0] https://support.office.com/en-us/article/Get-external-data-f...

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#192
post #142
post #66

Finance dev guy here. Excel's dominance in the field is because it is an _application container_ that _non_ dev people can use. The workflow is this: - old trader guy says to his junior guy: "hey can you look into xxx." - junior trader guy says: "sure I'll make a spreadsheet for it" - old trader guy: "great your model is all I need, let's trade" - several weeks later, IT guy says: "hey you're running a $100m book out…

At a former job I had to build a reporting system. And had the trust to build it however I wanted. But it was my job to make it sufficiently useful that everyone would use it. The smartest thing that I did was make it accessible from Excel. You could build a spreadsheet off of my report. Refresh the spreadsheet, the report ran, you got updated data. I got essentially 100% adoption, and the rest of my job was spent fi…

The best non-core feature I ever added to my SaaS service remains live import/export to Google Sheets.

My customers are not finance people, but they were using ad-hoc spreadsheets extensively and most of them through G suite. By implementing bidirectional sync for both master data and outputs they have the best of both worlds.

The Google Drive and Sheets APIs (and especially the on-change push notification callbacks) are not a pretty sight but once the twisty maze of nested data structures, webhooks and OAUTH2 and what-have-you were successfully navigated we got an efficient, complete and stable solution that everyone's happy with.

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#193
post #66

Finance dev guy here. Excel's dominance in the field is because it is an _application container_ that _non_ dev people can use. The workflow is this: - old trader guy says to his junior guy: "hey can you look into xxx." - junior trader guy says: "sure I'll make a spreadsheet for it" - old trader guy: "great your model is all I need, let's trade" - several weeks later, IT guy says: "hey you're running a $100m book out…

GS a decade ago had Excel connectors to its internal quant IDE/ORM/trading system.

It was pretty hacky, but you could pull prices for any security or book in the system, or call any options pricer, as long as the user had the right permissions.

Since the ORM had bindings to stuff like convex quadratic optimizers (IMSL), you could build spreadsheets that let users input linear constraints (e.g. I want to spend less than $50mm and buy between 0-5 units of X and 2-10 units of Y) and then plop the solution vector right back in their workflow. Basically magic to the user, and much better than Excel's 1D root-finder.

Most quants are notoriously bad at building UX, so this was often a much better solution than trying to write a dedicated tool from scratch.

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#195

Earlier quoted context omitted.

"How do we make a better spreadsheet..." Some things I'd like to see: - "Git for Excel" Diffs, tags, push changes to individual sheets or regions. - Snapshots: Create 'Time Machine' type interface for spreadsheet regions. - Collaborative editing. - Access control. - First-class support for model verification (folks roll their own at the moment).

Excel should have a built-in, top-notch SQL editor/IDE. Existing SQL IDEs return resultsets in a grid, thus, Excel is a natural fit. Excel would allow developers to go beyond a SQL IDE by subjecting the results to the rest of the spreadsheet's functionality. Would work great for exploratory analysis or for production reporting. Currently, running SQL and returning the results to a sheet is possible, but very cumberso…

Assuming your result set isn’t massive, it’s not very hard to have a 30-40 line array function (that takes a query) and returns a sql resultset.

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#196
post #17

Earlier quoted context omitted.

I can see this in my company. IT has for some reason decided to put some data into Hadoop. Now the users need to write spec documents for each little thing and every trivial report takes months to get done. Before they had Excel and could create whatever they needed in minutes. Us programmers don't always make life easier for end users. Often we create a level of bureaucracy. EDIT: I like a lot of the responses here.…

Us programmers also think about things like failure scenarios and disaster recovery. It may be trendy to criticize us for overcomplicating designs--and many do--but non-tech people using Excel are often doing more than just getting the job done...they're creating a disaster waiting to happen.

I wouldn't generalize too much. I once took a job where a $50 million in revenue a year business was running on a single AWS server that included everything (web, database, cache, etc.) with no backup of the data and the server was hand-maintained.

It was truly a disaster waiting to happen.

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#197

The issue in finance is the speed of change in demands. No formalized system platform with proper change control can keep up. The ability to play with data, the ability to break your work is valuable in end user computing and the type of thing that is hard to recreate in a formal financial system that must pass internal and external audits. edit: I entire a whole.

> hard to recreate in a formal financial system that must pass internal and external audits. This should also be the reason for getting rid of Excel, it is basically inauditable. But then again I guess all the auditing firms (e.g. Deloitte, KPMG, EY) probably have a vested interest in keeping it that way, not to mention all the persons who use this deniability for fudging numbers. I guess we'll always have ethics to…

I've worked in global banking environments (where one holding company contains a traditional banking arm and a securities firm) with a slight lean towards securities operations, although I've serviced both the banking and securities operations.

Your exact comment happens constantly in global banking firms. If the banking arm happens to be in a stronger management position, they push towards locked down system with tight development cycle controls. Typically the bankers have low risk tolerances and their systems (ex. savings and payment client account ledger systems, ATM ops, etc.) reflect it. That said, even in a true banking company, the closer the department is to the interest rating trading operations the more the banking environment looks like a securities operation. You will find critical on-the-fly modeling taking place, data integration, data reintegration all happening in Excel spreadsheets and these XLSX will be driving business decisions.

In securities operations the pace of change (demands originating from regulatory bodies, external clients, and internal trading operation, etc.) inevitably push the boundaries of what the bankers can support. If your securities side regulatory body says the firm must implement X in order to report Y, the firm is obligated to do it, regardless of what banking IT risk and change control framework the bank thinks should be followed. If the regulatory body says X should be in place by Z date, and banking assessment / change control procedures can not be finished by Z date, guess what X gets implemented by Z date. There is no choice. Same goes for client system demands. Woe be the IT staff who tries to tell a securities side broker their client demand can not be fulfilled by some change control / risk assessment procedure. Do such a thing enough and the desk's profitability will take a hit. Which means the trader's bonus will take a hit. Which means IT will take a hit.

If a proprietary / quant / interest rate trader can't respond quickly enough to market change X because it took 3 days for a revised real time report to be created and then vetted by a formal change management process, the IT staff pushing those processes will be replaced.

Looking at the situation another way, having these time sensitive demands in Excel instead of in trading systems is actually kind of smart. Try to integrate the rapid trading desk decision making that happens in Excel into the trading / transaction ledger / transaction settlement system will just endanger the smooth operation of the those systems. Fewer changes the better in these core systems.

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#198

Earlier quoted context omitted.

That's true. Good luck doing anything with that though. My main database was up to version 2051 by the time I left. The class I have nightmares about is the "compile and backup" class I wrote, that decompiled the database, appended the date to each file, compared all of the non-data and non-binary components, recompiled the database with the most recent versions, and then made sure every database was linked to the mo…

... wow. Instead of hashing the file contents, it just checked file size? Who came up with this algorithm?! There's plenty of applications that use files of fixed large sizes initialized at install, for performance reasons or just simply so if you're going to run out of space you do so during interactive installation, rather than silently breaking later on.

From what I can determine, the authoritative version was always the one with the later timestamp. This might have worked at least in theory, however there were always issues.

The foremost issue was that the timeserver was virtualized, and drifted to an unbelievable degree. This issue was compounded by the fact that we used citrix virtual machines for some applications, and our local desktops for other applications.

The latter issue wasn't a problem until they moved the citrix servers from an on-site server room to cloud provider about 40 miles away. (I think this is when the timeserver became virtualized as well) At that point the latency between the citrix sessions and the local desktops was such that I could create a document on the file share, edit it on my local desktop, close it, save it, and then open it on the remote desktop and it would lock the replication from my computer and lose all my work.

My department managed to wipe out the changes every other department had made to the yearly budget workbook because of this interaction. Of course part of the issue was using Excel as a shared document, but that issue was hugely compounded by the fact that our network infrastructure was bananas.

For instance, building access databases using the Microsoft recommended separate front-end/back-end files, with each individual user having their own front-end file actually managed to make the issue occur more frequently. If you used a single access database file on a shared drive the issue was substantially less likely to occur. I can only imagine this is because the dedupe was postponed until everyone was done using the file, and the file was more likely to substantially change in size when multiple people were using it.

The IT director had an incredibly fragile ego, and he attacked anyone who challenged him in any way. He hired a truck driver (with absolutely no IT experience) to work the IT help desk, which I assume was because he wanted a sycophant. He reported me to HR any time I did anything he didn't understand or which perceived to be a challenge of his power. He reported me to HR for using open-source software, with a description of open source software that made it sound like Wikipedia meets programming, and only used by hackers. He also reported me to HR after he heard I called our IT situation Kafkaesque.

As far as nightmares go, that might have been my favorite, because my defense was literally "it is Kafkaesque, and I can prove it." I managed to successfully convince HR that the IT situation was Kafkaesque using examples from Kafka's work, whose response was "well there isn't anything we can do about it, but you aren't wrong."

The sensible thing would have been to leave as soon as possible, but I really liked the field I was in, which was kind of specialized. Looking back I stayed way too long.

Re: Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands

#200
post #142
post #66

Finance dev guy here. Excel's dominance in the field is because it is an _application container_ that _non_ dev people can use. The workflow is this: - old trader guy says to his junior guy: "hey can you look into xxx." - junior trader guy says: "sure I'll make a spreadsheet for it" - old trader guy: "great your model is all I need, let's trade" - several weeks later, IT guy says: "hey you're running a $100m book out…

At a former job I had to build a reporting system. And had the trust to build it however I wanted. But it was my job to make it sufficiently useful that everyone would use it. The smartest thing that I did was make it accessible from Excel. You could build a spreadsheet off of my report. Refresh the spreadsheet, the report ran, you got updated data. I got essentially 100% adoption, and the rest of my job was spent fi…

Yep, the most useful program I ever wrote for business was a Perl script that ran SQL against a Sybase database and sent the output as a spreadsheet. Multiple return sets went on multiple sheets. Took the column names from the return sets and did decent formating of data. Then the user would do all the fun stuff.
Post reply on HN