4 ms·

I disagree. Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. The only practic

by twunde 10y ago

I disagree. Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. The only practical downsides to his use of Excel it's that he doesn't have direct database access so we generally need to create the initial reports to give him the data and secondly Excel can only hold a little over 2M rows.

blahi 10y ago
It can hold many more if you use Power Pivot.
- twunde 10y ago
  Good to know.
throw_away_777 10y ago
How do you verify that his calculations are correct?
- twunde 10y ago
  His calculations are usually compared to invoices (ie do the numbers match what we were charged for), other reports and historical data. Similarly when one of our DBAs or BAs creates an important report, we check the results against other data sources.
- shostack 10y ago
  Why do you think this can't be verified? You can see the formulas in cells, or you can see the macro code. Further, you could auto import older data inputs and outputs via data connections in Excel and set up some automated checks to flag if something is off. Is there a use case where you think this wouldn't be possible?
  - throw_away_777 10y ago
    You can verify it sure, but it is much harder to verify excel equations than code. There is no github for Excel (besides saving with different names). You generally don't see the equations when you look at Excel - this is one of the reasons Excel is so error prone.
    - shostack 10y ago
      Fully agree. My point was that it can indeed be verified though in many circumstances. That said, I think there's always a balance between a quick down and dirty business solution that gets the job done, vs. something fully engineered. Additionally, it is much easier for business users to shoulder more of the workload while letting people with programming knowledge focus on other tasks.
rabboRubble 10y ago
The data, information layouts, and APIs provided by information brokers are well tested with Excel and straight forward. Excel really shines when used in financial environment. The problems with Excel appear when the incoming data is "dirty". Outside the finance industry there less financial incentive to ensure data is reliably interpreted in Excel.
fnord123 10y ago
>Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. We used to buy in signals from a company who did their work in Excel. I wrote some scripts to export the data and recalculate it in Python. Almost every month I found errors in their reports and had to ask them to fix it. So I recommend you fight HARD to get someone to reproduce his work in a language that is visible and reproducible.
- nightski 10y ago
  Your conclusion is that it was excel causing these errors and you are implying similar errors would not be made in Python. I think it is more because of your experience as a developer why you were able to spot and correct errors.
  - dandermotj 10y ago
    It is absolutely the interface to excel that causes errors. I can take your excel spreadsheet, format cells, add things, change a reference and generally fuck it up and hand it back to you and you would never know. Whereas, if I change a text file then you can see what's changed. Similar errors are much less common in a programming language.d
    - twunde 10y ago
      When you're working with people who are good with Excel, they will notice. If you're only ok with excel and just use vlookups, then you're probably going to have problems, especially since those people don't tend to keep a versioned history of their files. Really the main difference is that version control has become an ingrained habit in software development whereas with Excel it's rare
      - xapata 10y ago
        Ah, a True Scotsman is good at Excel. I understand now.
dandermotj 10y ago
So just to give you an opposing perspective of what you just said: "we have __one__ person that does all...". So apart from the fact he can't access a database appropriately, he's the _only_ person who does this work and none of it is documented or reviewable because that's the nature of Excel. There's some serious operational risk in that. This guy disappears tomorrow and what do you do?
- twunde 10y ago
  It's a misconception that work in Excel can't be documented or reviewed. We have the seed data he uses and the resulting Excel files (with the formulas he used still in them). This is the equivalent to version control for programming. The results are also reviewed against 1-2 appropriate sources (invoices, historical data, multiple other reports built by IT) for accuracy. To your very legitimate point that he's the only one doing this work, if he left, long-term we would probably need 3-4 people to take over his work. Short-term, the company would probably need to put together a triage team of 5-6 people to take over his existing processes. We actually moved some of the hedging to a different team last year and we had to hire two business analysts and implement a industry-specific system to do so.
  - gjm11 10y ago
    > if he left, long-term we would probably need 3-4 people to take over his work. I hope you're paying him really well.
    - fnord123 10y ago
      Or they could drop him to sort out the operational risk aspects.
  - euyyn 10y ago
    I don't understand your version control equivalence. If someone introduces a non-obvious bug in his formulas, how can you discover it, and restore the file to its previous good state?
lordnacho 10y ago
Why does he not simply read a tutorial on how to pull in data from a database?
- twunde 10y ago
  I think it comes down to the fact that he hasn't needed to and that he prefers the tools he already knows. He's also not part of the IT department, so it would be similar to having your CFO have direct database access. Possible but probably not the best use of time.
nerdponx 10y ago
I'm pretty sure there is an ODBC plug-in for Excel
- ygra 10y ago
  I understood it as that the person didn't have database access, not Excel.
nerdponx 10y ago
I'm pretty sure there is an ODBC plug-in for Excel
bereasonable 10y ago
Excel can handle millions of rows if he brings the data into PowerPivot. Note: I'm a PM on Excel
- newman314 10y ago
  Random point but Excel sure could use a CONTAINS function for substring matches. I was looking for something like this yesterday and the solutions I found were quite ugly. Ended up having to use DSUM/DCOUNT instead which is still inelegant when one has to to multiple lookups with slightly varying parameters.
  - bereasonable 10y ago
    We're planning on adding support for regex. The current state of the work is shown on our User Voice site. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9022339-built-in-regex-regular-expressions-functions-to https://excel.uservoice.com/forums/304921-excel-for-windows-...

by twunde 10y ago

blahi 10y ago

It can hold many more if you use Power Pivot.

twunde 10y ago
Good to know.

throw_away_777 10y ago

How do you verify that his calculations are correct?

twunde 10y ago
His calculations are usually compared to invoices (ie do the numbers match what we were charged for), other reports and historical data. Similarly when one of our DBAs or BAs creates an important report, we check the results against other data sources.
shostack 10y ago
Why do you think this can't be verified? You can see the formulas in cells, or you can see the macro code. Further, you could auto import older data inputs and outputs via data connections in Excel and set up some automated checks to flag if something is off. Is there a use case where you think this wouldn't be possible?
- throw_away_777 10y ago
  You can verify it sure, but it is much harder to verify excel equations than code. There is no github for Excel (besides saving with different names). You generally don't see the equations when you look at Excel - this is one of the reasons Excel is so error prone.
  - shostack 10y ago
    Fully agree. My point was that it can indeed be verified though in many circumstances. That said, I think there's always a balance between a quick down and dirty business solution that gets the job done, vs. something fully engineered. Additionally, it is much easier for business users to shoulder more of the workload while letting people with programming knowledge focus on other tasks.

rabboRubble 10y ago

The data, information layouts, and APIs provided by information brokers are well tested with Excel and straight forward. Excel really shines when used in financial environment. The problems with Excel appear when the incoming data is "dirty". Outside the finance industry there less financial incentive to ensure data is reliably interpreted in Excel.

fnord123 10y ago

>Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. We used to buy in signals from a company who did their work in Excel. I wrote some scripts to export the data and recalculate it in Python. Almost every month I found errors in their reports and had to ask them to fix it. So I recommend you fight HARD to get someone to reproduce his work in a language that is visible and reproducible.

nightski 10y ago
Your conclusion is that it was excel causing these errors and you are implying similar errors would not be made in Python. I think it is more because of your experience as a developer why you were able to spot and correct errors.
- dandermotj 10y ago
  It is absolutely the interface to excel that causes errors. I can take your excel spreadsheet, format cells, add things, change a reference and generally fuck it up and hand it back to you and you would never know. Whereas, if I change a text file then you can see what's changed. Similar errors are much less common in a programming language.d
  - twunde 10y ago
    When you're working with people who are good with Excel, they will notice. If you're only ok with excel and just use vlookups, then you're probably going to have problems, especially since those people don't tend to keep a versioned history of their files. Really the main difference is that version control has become an ingrained habit in software development whereas with Excel it's rare
    - xapata 10y ago
      Ah, a True Scotsman is good at Excel. I understand now.

dandermotj 10y ago

So just to give you an opposing perspective of what you just said: "we have __one__ person that does all...". So apart from the fact he can't access a database appropriately, he's the _only_ person who does this work and none of it is documented or reviewable because that's the nature of Excel. There's some serious operational risk in that. This guy disappears tomorrow and what do you do?

twunde 10y ago
It's a misconception that work in Excel can't be documented or reviewed. We have the seed data he uses and the resulting Excel files (with the formulas he used still in them). This is the equivalent to version control for programming. The results are also reviewed against 1-2 appropriate sources (invoices, historical data, multiple other reports built by IT) for accuracy. To your very legitimate point that he's the only one doing this work, if he left, long-term we would probably need 3-4 people to take over his work. Short-term, the company would probably need to put together a triage team of 5-6 people to take over his existing processes. We actually moved some of the hedging to a different team last year and we had to hire two business analysts and implement a industry-specific system to do so.
- gjm11 10y ago
  > if he left, long-term we would probably need 3-4 people to take over his work. I hope you're paying him really well.
  - fnord123 10y ago
    Or they could drop him to sort out the operational risk aspects.
- euyyn 10y ago
  I don't understand your version control equivalence. If someone introduces a non-obvious bug in his formulas, how can you discover it, and restore the file to its previous good state?

lordnacho 10y ago

Why does he not simply read a tutorial on how to pull in data from a database?

twunde 10y ago
I think it comes down to the fact that he hasn't needed to and that he prefers the tools he already knows. He's also not part of the IT department, so it would be similar to having your CFO have direct database access. Possible but probably not the best use of time.

nerdponx 10y ago

I'm pretty sure there is an ODBC plug-in for Excel

ygra 10y ago
I understood it as that the person didn't have database access, not Excel.

nerdponx 10y ago

I'm pretty sure there is an ODBC plug-in for Excel

bereasonable 10y ago

Excel can handle millions of rows if he brings the data into PowerPivot. Note: I'm a PM on Excel

newman314 10y ago
Random point but Excel sure could use a CONTAINS function for substring matches. I was looking for something like this yesterday and the solutions I found were quite ugly. Ended up having to use DSUM/DCOUNT instead which is still inelegant when one has to to multiple lookups with slightly varying parameters.
- bereasonable 10y ago
  We're planning on adding support for regex. The current state of the work is shown on our User Voice site. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9022339-built-in-regex-regular-expressions-functions-to https://excel.uservoice.com/forums/304921-excel-for-windows-...