9 ms·

Most financial firms need their quants to worry less about the new programming language hotness, and more about moving entire systems off unbelievably complicat

by s_q_b 10y ago

Most financial firms need their quants to worry less about the new programming language hotness, and more about moving entire systems off unbelievably complicated Excel spreadsheets.

markovbling 10y ago
Totally - I used to work as quant for a boutique asset manager and the whole business was running on spreadsheets. Insane. Ended up putting it all in a database and developing an excel add-in to pull it from the database as array formulae. Used a great library called Excel DNA to develop the add-in using C# if anyone is interested.
- sandGorgon 10y ago
  Just a question-did you ever consider using Jupiter Notebooks? Or RMarkdown Notebook?
  - markovbling 10y ago
    Only jumped on the python train after I left finance but the reason we used an add-in is because you can build dynamic sheets with calculations that update when the inputs change (where the inputs were pulling from the database). So you could build a sheet that pulls in portfolio holdings for yesterday where yesterday updates each day and then compute performance and risk stats referencing the data cells in the sheet and it would all update. In that context it was just an easy way to build reports pulling data from a database but same applies to quickly doing one-off analysis in Excel pulling dynamic data from the database - guys in finance tend to not be programmers but they're really good at Excel. The add-in approach was really useful too because you could create function that returns the holdings of a portfolio to an array of cells (an array formula) and have a drop-down box with all portfolios that fed the input of the formula so that when you change the combo box, it changed the portfolio data and then everything recalculated off the back of that :)
wz1000 10y ago
IIRC, Standard Chartered achieved this by initially adding Haskell interop to Excel[0] and then moving to a custom GUI solution to replace Excel altogether. [0]- https://www.youtube.com/watch?v=hgOzYZDrXL0 https://www.youtube.com/watch?v=hgOzYZDrXL0
thearn4 10y ago
I work in aerospace engineering, and it's definitely the same here. In scientific research or engineering these days, there are a lot of potential steps up from excel spreadsheet hell or spaghettified MATLAB code. Bonus points for the facilitation of any type of documentation, automated testing, or version control.
valarauca1 10y ago
The reason for this is simple. Excel is 1. An incredibly powerful tool. 2. No bar of entry (cost aside, true in corporate environment). 3. Very gradual learning curve. 4. The efficiency gain vs time invested is exponential. Power Excel users, much like their VIM/Emacs counter parts don't use a mouse. It is just keyboard short cuts [1][2]. This makes them insanely productive. [1] https://youtu.be/jFSf5YhYQbw https://youtu.be/jFSf5YhYQbw [2] https://youtu.be/0nbkaYsR94c https://youtu.be/0nbkaYsR94c
- walshemj 10y ago
  There are also many downsides
  - gravypod 10y ago
    You can say that about every technology but the question is if the good out ways the bad in your specific usecase.
  - valarauca1 10y ago
    Yes and No. If all the data you receiving is also coming to you as an Excel format (csv, xls, xlsx), but with major differences in formatting, or wholly inconsistent formatting. Now you have a multi-month long project just to have a consistent import script. Replacing a 1 second task done 2-3's times a day with a 4month project has an ROI on the scale of decades. Not worth it. Then you add visualization. What is 3-4 keystrokes in Excel is a lot of back of forth, learning a new library, ensuring it works on your system. Vetting the visualizing, dealing with that weird bug on the triple line double axis line chart. Then you have to validate integer handling and mathematics to ensure your newly written Python, Julia, etc. handles the same as your well vetted Excel Spread Sheet. Replacing that one slow bloated spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel.
    - Swizec 10y ago
      > spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel. And now you have a scalable system. You can go from something one employee takes all day to look at 2x/day, to something anyone in the company can see in real time on a dashboard of some sort. Is that worth it? Depends
SnowingXIV 10y ago
Excel is incredibly useful and powerful. This type of comment screams "I've never used Excel a day in my life for anything other than creating a table." It can handle very complex formulas, that are easy to follow, and the data manipulation and efficiency is amazing. Your argument for moving away from Excel is the same as those who don't develop and say everything should be done through a WYSIWG editor.
- goatlover 10y ago
  Excel use should have an inverse relationship with complexity. Just because you can make Excel handle complex formulas doesn't mean you should, or that's it's the right tool for the job.
- saretired 10y ago
  I've been around since Lotus 1.0, and worked in financial and engineering firms. I've seen cases where spreadsheets have been the right answer for knowledgeable and relativity sophisticated users, either to build a quick model or as a front-end, and cases where the result is an unauditable mess. Lots of oops when say accounting people don't understand say the math of partial-period NPVs, or are so innumerate that an obviously wrong result looks fine to them. Without the review process that should go along with production code, sometimes you get lucky, sometimes you don't. It all comes down to who is using the tool, I guess.
TuringNYC 10y ago
There are many technical arguments on both sides, but the business argument i've been given is that Excel sheets are the most audit-able, especially when they are self-contained. Auditors like this, especially after Sarbanes Oxley. Excel sheets fall into a different audit classification as compared to "systems" (a python script might be considered a "system".)
fma 10y ago
I know a software engineer who worked for one of the big quant firms in the north east (forgot the name...it's big) before people even knew what quants were. He worked for them till he got out on his own. All his backtracking software is written by him and is in C (nice GUI, graphing feature, etc). He uses it to find his edge. His trading platform is Excel...Obviously he doesn't do HFT...his trades are measured in days. I know - 1 data point, but if a software engineer who is better than me in both trading & coding is using Excel, I'm not going to knock it.
lordnacho 10y ago
I've worked in financial firms my whole career, and I agree. Excel is useful in one particular case only: when you don't want to build a GUI. It's great as a not-very-pretty interface for functionality written in DLLs. For any process that's well thought out, you can write a Python script if it's not time critical. And it probably isn't if you were doing it in Excel. The main problem with Excel is it's too easy to write an ad-hoc fix. Sounds like a weird reason, but in finance they just pile up and up and up. Finance Excel users also tend to know just enough coding to dig a huge hole, and just little enough to not understand this. Soon you have an unauditable mess, and the business is almost never going to spend time paying up technical debt. There's also the philosophical issue of ever more complex models. If you have some sane coding practices, you will tend to favour more elegant code. Balls of spaghetti are more obvious in something like Python. More elegant code is connected to more elegant models. Inelegant models, such as the ones often bragged about by M&A guys (let's be honest, they're sales tools, not predictions) when written into an ordinary language, will look like the balls of spaghetti that they are.
sgt101 10y ago
It's all part of agility vs. efficiency, but not in the way people think! If people are heavily utilized they turn to Excel because they can get through a few simple things really fast. There's risk to using things like R or Julia because you can't see (literally) how to do it, or what you can do, and trying something different will earn you a rapid sacking at the hands of the super utilizers. But pretty soon you are mired in spreadsheet hell. Nothing can be seen or understood, everything is invalid or valid - who knows and worst of all when something stops working you don't know why. And you don't know when it will stop. Goodbye agility! Any spreadsheet with more that 2 days of work to reproduce it should be counted as IT and put on a formal risk register until it is recoded and removed. But dream on..
arca_vorago 10y ago
Does anyone know if the way complicated/advanced formulas are handles in libreoffice would make it less suitable for these tasks? They seem to have ironed out most of the bugs, so I wonder if it would be worth pushing these quants who can program into the libre scene to get them to contribute back to projects? Of course if whatever the backend is handling formulas in something like libreoffice truly is subpar, that will never happen.
branchless 10y ago
Spreadsheets where functions are entirely based on cell formulas publishing to bespoke internal data busses with in-house plugins that randomly stop working. There can be no god.

by s_q_b 10y ago

Most financial firms need their quants to worry less about the new programming language hotness, and more about moving entire systems off unbelievably complicated Excel spreadsheets.

markovbling 10y ago

Totally - I used to work as quant for a boutique asset manager and the whole business was running on spreadsheets. Insane. Ended up putting it all in a database and developing an excel add-in to pull it from the database as array formulae. Used a great library called Excel DNA to develop the add-in using C# if anyone is interested.

sandGorgon 10y ago
Just a question-did you ever consider using Jupiter Notebooks? Or RMarkdown Notebook?
- markovbling 10y ago
  Only jumped on the python train after I left finance but the reason we used an add-in is because you can build dynamic sheets with calculations that update when the inputs change (where the inputs were pulling from the database). So you could build a sheet that pulls in portfolio holdings for yesterday where yesterday updates each day and then compute performance and risk stats referencing the data cells in the sheet and it would all update. In that context it was just an easy way to build reports pulling data from a database but same applies to quickly doing one-off analysis in Excel pulling dynamic data from the database - guys in finance tend to not be programmers but they're really good at Excel. The add-in approach was really useful too because you could create function that returns the holdings of a portfolio to an array of cells (an array formula) and have a drop-down box with all portfolios that fed the input of the formula so that when you change the combo box, it changed the portfolio data and then everything recalculated off the back of that :)

wz1000 10y ago

IIRC, Standard Chartered achieved this by initially adding Haskell interop to Excel[0] and then moving to a custom GUI solution to replace Excel altogether. [0]- https://www.youtube.com/watch?v=hgOzYZDrXL0 https://www.youtube.com/watch?v=hgOzYZDrXL0

thearn4 10y ago

I work in aerospace engineering, and it's definitely the same here. In scientific research or engineering these days, there are a lot of potential steps up from excel spreadsheet hell or spaghettified MATLAB code. Bonus points for the facilitation of any type of documentation, automated testing, or version control.

valarauca1 10y ago

The reason for this is simple. Excel is 1. An incredibly powerful tool. 2. No bar of entry (cost aside, true in corporate environment). 3. Very gradual learning curve. 4. The efficiency gain vs time invested is exponential. Power Excel users, much like their VIM/Emacs counter parts don't use a mouse. It is just keyboard short cuts [1][2]. This makes them insanely productive. [1] https://youtu.be/jFSf5YhYQbw https://youtu.be/jFSf5YhYQbw [2] https://youtu.be/0nbkaYsR94c https://youtu.be/0nbkaYsR94c

walshemj 10y ago
There are also many downsides
- gravypod 10y ago
  You can say that about every technology but the question is if the good out ways the bad in your specific usecase.
- valarauca1 10y ago
  Yes and No. If all the data you receiving is also coming to you as an Excel format (csv, xls, xlsx), but with major differences in formatting, or wholly inconsistent formatting. Now you have a multi-month long project just to have a consistent import script. Replacing a 1 second task done 2-3's times a day with a 4month project has an ROI on the scale of decades. Not worth it. Then you add visualization. What is 3-4 keystrokes in Excel is a lot of back of forth, learning a new library, ensuring it works on your system. Vetting the visualizing, dealing with that weird bug on the triple line double axis line chart. Then you have to validate integer handling and mathematics to ensure your newly written Python, Julia, etc. handles the same as your well vetted Excel Spread Sheet. Replacing that one slow bloated spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel.
  - Swizec 10y ago
    > spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel. And now you have a scalable system. You can go from something one employee takes all day to look at 2x/day, to something anyone in the company can see in real time on a dashboard of some sort. Is that worth it? Depends

SnowingXIV 10y ago

Excel is incredibly useful and powerful. This type of comment screams "I've never used Excel a day in my life for anything other than creating a table." It can handle very complex formulas, that are easy to follow, and the data manipulation and efficiency is amazing. Your argument for moving away from Excel is the same as those who don't develop and say everything should be done through a WYSIWG editor.

goatlover 10y ago
Excel use should have an inverse relationship with complexity. Just because you can make Excel handle complex formulas doesn't mean you should, or that's it's the right tool for the job.
saretired 10y ago
I've been around since Lotus 1.0, and worked in financial and engineering firms. I've seen cases where spreadsheets have been the right answer for knowledgeable and relativity sophisticated users, either to build a quick model or as a front-end, and cases where the result is an unauditable mess. Lots of oops when say accounting people don't understand say the math of partial-period NPVs, or are so innumerate that an obviously wrong result looks fine to them. Without the review process that should go along with production code, sometimes you get lucky, sometimes you don't. It all comes down to who is using the tool, I guess.

TuringNYC 10y ago

There are many technical arguments on both sides, but the business argument i've been given is that Excel sheets are the most audit-able, especially when they are self-contained. Auditors like this, especially after Sarbanes Oxley. Excel sheets fall into a different audit classification as compared to "systems" (a python script might be considered a "system".)

fma 10y ago

I know a software engineer who worked for one of the big quant firms in the north east (forgot the name...it's big) before people even knew what quants were. He worked for them till he got out on his own. All his backtracking software is written by him and is in C (nice GUI, graphing feature, etc). He uses it to find his edge. His trading platform is Excel...Obviously he doesn't do HFT...his trades are measured in days. I know - 1 data point, but if a software engineer who is better than me in both trading & coding is using Excel, I'm not going to knock it.

lordnacho 10y ago

I've worked in financial firms my whole career, and I agree. Excel is useful in one particular case only: when you don't want to build a GUI. It's great as a not-very-pretty interface for functionality written in DLLs. For any process that's well thought out, you can write a Python script if it's not time critical. And it probably isn't if you were doing it in Excel. The main problem with Excel is it's too easy to write an ad-hoc fix. Sounds like a weird reason, but in finance they just pile up and up and up. Finance Excel users also tend to know just enough coding to dig a huge hole, and just little enough to not understand this. Soon you have an unauditable mess, and the business is almost never going to spend time paying up technical debt. There's also the philosophical issue of ever more complex models. If you have some sane coding practices, you will tend to favour more elegant code. Balls of spaghetti are more obvious in something like Python. More elegant code is connected to more elegant models. Inelegant models, such as the ones often bragged about by M&A guys (let's be honest, they're sales tools, not predictions) when written into an ordinary language, will look like the balls of spaghetti that they are.

sgt101 10y ago

It's all part of agility vs. efficiency, but not in the way people think! If people are heavily utilized they turn to Excel because they can get through a few simple things really fast. There's risk to using things like R or Julia because you can't see (literally) how to do it, or what you can do, and trying something different will earn you a rapid sacking at the hands of the super utilizers. But pretty soon you are mired in spreadsheet hell. Nothing can be seen or understood, everything is invalid or valid - who knows and worst of all when something stops working you don't know why. And you don't know when it will stop. Goodbye agility! Any spreadsheet with more that 2 days of work to reproduce it should be counted as IT and put on a formal risk register until it is recoded and removed. But dream on..

arca_vorago 10y ago

Does anyone know if the way complicated/advanced formulas are handles in libreoffice would make it less suitable for these tasks? They seem to have ironed out most of the bugs, so I wonder if it would be worth pushing these quants who can program into the libre scene to get them to contribute back to projects? Of course if whatever the backend is handling formulas in something like libreoffice truly is subpar, that will never happen.

branchless 10y ago

Spreadsheets where functions are entirely based on cell formulas publishing to bespoke internal data busses with in-house plugins that randomly stop working. There can be no god.