Migrating from Excel / VBA Workbook - Technology Update

Question

It will be more a matter of opinion, because I can not find an answer on Google or on any SME in my company that really have an opinion on this.

I was not sure that the opinion question was appropriate, therefore, if it is not, please feel free to delete it, prohibit me or anything that the rules may dictate.

I am working on a large set of very old books that are used for mathematical calculations, they are not used in Production, they are designed to check the results of the production service. The workbooks are very old (about 12 years old) and now I have called “clutter” from the last decade of corrections / corrections.

I studied the Handsontable ( https://handsontable.com/ ) JS library, which provides a spreadsheet interface such as Excel that would handle all the setting of cell values, etc. Then use Python (or Java, if the company prefers to use J2EE) to perform some heaver calculations (search and data forecasts) on the server side or on the client side, I feel that just switching from VBA and Excel itself will provide a performance increase that Operations will just to love.

I feel that the initial work will be many hours, but I believe that I can create a common set of libraries either in JS / Python / Java, which once complete, will reduce a lot of the transcoding that will be needed to execute on the VBA side on JS / Python since 80% of VBAs are general financial formulas.

I’m afraid that if we continue with the current textbooks in another 2-3 years, they will become completely insecure, as well as the fact that with VBA we do not have proper version control or use modern automated code analysis tools that we can extract out of use, using new technologies and a long wrong will, hard work will be the best option for our developers now and for my future colleagues.

If this question is approved by the rules and moderators, does anyone have an opinion about this? It costs? It's a waste of time?

Note. I am not a manager, nor a director, nor anyone who can make a decision, as suggested above, I am just a developer who feels that I ... We can do better.

Thank you for your time.

Answer 1

Thus, this may not be the answer for everyone, but I am going to give you what we decided to invest during the first, since this method was already used in another application to move it from the old language to Java.

We found several open source projects online to convert VB / VBA code to Python.

Examples:

• http://vb2py.sourceforge.net/
• https://pypi.python.org/pypi/vb2py

As soon as we get Python code that works using our own package to allow access to MS Interop, we will begin to reorganize the Python code into a Django project (this choice is not strong, but its my preferred choice and <3 Django) including creating wrapper models for access to the database. We feel that we can make a shell for the MS Interop part so that it can work as a Django project.

In the end, the plan should be for the Django project to act as soon as the REST web service to perform the functions of "heavy mathematics / computation" in Python, and also could be on a much more powerful server, increasing productivity. This REST service would answer calls from the help system ( https://handsontable.com/ ) of JS Spreadsheet with the necessary plug-ins for processing all current Excel formulas so that we can make a direct copy of our values ​​/ styles in 1: 1 (convert the workbook in xlsm, so that all data stored as XML) is in an array of Handsontable layouts; which, I think, we will put in the database. Thus, in the end, we now released workbooks lasting more than 15 years and completely removed Excel from the equation.

Current (my) reasons for using Django:

• ORM
• Independent Django packages simplify pruning shrinking code (I know this can only be done with pure Python, but I find it a good reason to answer other questions).
• Ease of implementing a web service using several very popular REST packages.
• There are others, but I believe that everyone will get my points.

And the reasons for Python:

• It works on Linux, for server-side operation VBA Macros requires Windows (bad: the cost of a VM compared to Linux and less OS overhead).
• Very easy to learn (I still feel)
• Custom method decoders will apply things like standard interest rates to the return amount of the method without requiring the interest code in the actual function that calculates the amounts (as an example)

thanks