5

This is going to be more of a opinion question because I can't find any answer on Google or any SME's within my company that really have a opinion on this.

I wasn't sure if a opinion question is appropriate, so if its not please feel free to delete this, ban me or whatever the rules may dictate.

I'm work on a large set of very old Workbooks that are used for math calculations, these are not used in Production they are for check the results of the Production service. The Workbooks are very old (some 12+ years) and are now what I would call a 'mess' from the last decade of patching/fixes.

I've been researching Handsontable (https://handsontable.com/) a JS library that provides a spreadsheet interface like Excel that would handle all of the setting of the values of the cells etc... Then use Python (or Java if the company prefers for use J2EE) to do some of the heaver calculations (data lookup and projections) on the server side, or client side, I feel that just moving from VBA and Excel itself will provide a performance boost which Operations would just love.

I feel that the initial work would be a lot of hours, but I believe that I can create a common set of library's either in JS/Python/Java that will once completed reduce a lot of the re-coding that would need to be done on the VBA to JS/Python side as 80% of the VBA is common financial formulas.

My fear is that if we continue on the path with the current Workbooks in another 2-3 years they will become completely un-maintainable, alot with the fact that with VBA we don't have proper version control or the use of modern automated code review tools which we can benefit from by using newer technologies and in the long wrong will, hard work aside will prove to be the best option for our developers now and for my future colleagues.

If this question is ok by the rules and moderators, does anyone have any opinion on this? Is this worth while? Is it a waste of time?

Note: I'm not a manager, director or anyone with the power to make a decision as proposed above, I'm just a developer that feels I.... We can do better.

Thank you for your time.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Tempster102
  • 183
  • 3
  • 10
  • CSV for the data storage is IMHO the way to go. Usable by any program, and highly compatible with excel – Pierre Dec 30 '16 at 10:45
  • It's probably not an appropriate question for SO, (Guess it belongs on like programmers or such) but it's still a good question. We did exactly the same exercise. Extracted all the database related code from the VBA and moved it into a service API we can call with ajax. The excels themselves were made into a module in our app. We wrote the app first and then just started converting each report one by one. By replacing very slow excel reports with a fast app we already save close to an hour per person per day in administration work. Also, higher management loves mobile friendly reporting. – Shilly Dec 30 '16 at 10:48
  • Let me clarify, we already have a DB in place the Production/Workbooks apps use so its not so much the data its VBA, Formulas etc... I just looking to modernize the Workbooks to prevent to much technical debt building up, cause we will get to a point where we look at the Workbooks and are like "WTF is this"? – Tempster102 Dec 30 '16 at 10:51
  • 1
    In my personal opinion, moving away from excel was the best part, but that's mainly due to that we didn't need any of the excel functionality in the reports. Our Ops never need to write an excel formula in their reports, they basically just use the excel as a way to view the database and do some pre-formatted calcualtions on production data. With optimised SQL, a fast backend and a lightweight app library, we can now offer quasi real time calculations. If your Ops need to be able to use non-trivial excel functionality, something like office 365 or sharepoint could be better. – Shilly Dec 30 '16 at 11:08
  • 1
    I've done a survey of about 150 onshore/offshore Ops guys and most never really manually edit cells unless they are providing a static Workbook for a 'correct' reference for the Production servers (e.g. the correct amounts) so really its just a static layout, with several sheets that have defined names that are then set in VBA; with some being done via formulas but there are JS library's that can handle all the Excel formulas so thats a non-issue. And then coverting the Workbooks to XLSM makes the sheets CVS so I can do a direct convert to a Handsontable array :) the hard part is the VBA. – Tempster102 Dec 30 '16 at 11:13
  • I ran out of space in the above. But thank you for your response! I've got some more prototyping to do to see what best fits in with our budget and what our managers want. – Tempster102 Dec 30 '16 at 11:15
  • @Shilly; I've estimated based on the average calculation time was 3-4mins per calculation so I estimate the between Ops and regression we can save 1000+ hours a year. I'm just not sure if I should look at moving away from Excel, or adapt what we currently have; what I called 'retro-fitting' to speed some things up but not totally re-write it as you explained. As for mobility, this is not something that would be used outside of the Corp VPN so it would be just an internal app Thanks! – Tempster102 Dec 30 '16 at 11:23

1 Answers1

0

So this may not be the answer for everyone, but I'm going to give you what we decided to invest time in first since this method was already used on another application to move it from a an old language to Java.

We have found several opensource projects online for the conversion of VB/VBA code to Python.

Examples:

Once we have the Python code working using the native package to allow MS Interop access we will begin to refactor the Python code into a Django project (this choice isn't firm, but its my preferred choice; <3 Django) including creating the wrapper models for DB access. We feel we can make a wrapper for the MS Interop part to allow it to run as a Django project.

The plan in the end is to have the Django project act as only a REST Web Service for doing the 'heavy math/calculations' functions to Python and also could be on a much more powerful server increasing performance all around. This REST Service would answer calls from a Handsontable (https://handsontable.com/) JS Spreadsheet with the required plugins to handle all the current Excel formulas so we can do a direct 1:1 copy of our cell values/styles (convert Workbook to xlsm makes all the data stored as XML) to a Handsontable 'layout array'; which I think we will put in a DB. So in the end we have now given out Workbooks 15+ years of life and have removed Excel from the equation all together.

The current (mine) reasons for using Django are:

  1. ORM
  2. Independent Django packages makes reducing reduent code easier (I know this could be done with just pure Python but I feel its a good reason to put in my answer for others)
  3. Easy to implement a Web Service using one a few very popular REST packages.
  4. There are others but I believe everyone will get my points.

And the reasons for Python:

  1. Runs on Linux, running VBA Macros server side requires Windows (bad: VM Cost when compared to Linux and less OS overhead).
  2. Very easy to learn (I feel anyway)
  3. Custom method decorators will make apply such things as standard interests rate to the return amount of a method without needing the interest rate code in the actual function that calculations amounts (as an example)

Thanks

Tempster102
  • 183
  • 3
  • 10