I have slowly but surely been moving simple processes/reporting from Excel onto servers. The aim being that any spreadsheets (yuck) that people use are basically visualisation mechanisms for processes and data living on servers (where programmers can look after then).What this means in practice:- there is VBA that automatically formats according to data delivered (people should not have to fiddle about with formatting FFS)- there is lightweight (as clean as possible) version controlled VBA that communicates with the servers. Outlawed is:- any SQL more complex than 'select stuff from view'- any computation in the VBA beyond that necessary for the application logic itself (i.e. no financial calculations or data manipulation)- add hoc spreadsheets scattered around the place Very minimal use of formulas (only when essential).This has been very helpful. Things are more reliable and faster. Now the question:For separate reasons I have developed some simple python packages to perform routine operations on the servers and databases. The aim is basically that these operations are completely abstracted away and as and when we need to update them, then they are added in properly.Now I know that this may be a divisive approach: the 'object relational impedance mismatch' issue. But in our context it works pretty well. The approach is basically to treat the whole database/server as simply a collection of objects by the time it gets the end-user: and objects that behave pretty much as one would expect in the client environment. Its not exactly the same as using an ORM since it is more restrictive by design. The idea being that you cant do something unless a view or stored procedure for that thing exists and this constitutes a kind of interface. The aim is not to map the database to an object, but to have the database emulate a simple set of interfaces. Problem is that I find my self starting to write the same abstractions in the VBA as in the python code. This is not the end of the world, but it does mean there are two parallel streams of code doing similar jobs, which is not ideal. So I was thinking about just writing even lighter weight VBA that communicates with the python routines on the servers. Then I can implement the plumbing only once and in a more suitable language (imho).Has anyone ever done something like this? Is it silly? Alternatively I guess one could write libraries in C++ and port them around the place... but this would be more work than is sensible for these things and python actually does a fantastic job of wrapping the interface presented by the database in a well behaved fashion (I actually really enjoyed writing it, which is always a good sign).I would appreciate any thoughts or criticisms.EDIT:I just came across this alternative approach... but not sure how robust this would be:compile python to dllAlso there are always issues with IT shitting the bed any time you want to put stuff on all the computers (especially if it is anything to do with open source and is named after a predatory snake). The advantage with keeping it all server side is that we don't have those issues.

Hello, I don't have experience of gluing together Python and VBA but I do have experience gluing Python and C++, Python and Fortran, and I have used things like py2exe and cxfreeze in the past for various things. In general doing this kind of thing has not been very fun, it is a lot of fiddling around and you reach the 'rough edges' of the Python ecosystem where problems are plentiful and answers are scarce.If I understand your predicament correctly, one approach might be to run a web server with a Rest API on your server machine, which the VBA client communicates with using http requests. Then your VBA code is receiving data in the clean representation wrapped by your lightweight ORM. In other words, you make a 'webapp'. This is actually pretty easy to do with well supported Python web frameworks such as Flask, Tornado and Django (listed roughly in order of ease of use) or if you prefer it is fairly easy to roll your own with the Python standard library. But this additional layer might complicate things, and of course it would introduce other (different) problems.

We have a similar setup, just wanted to clarify why is there a need to actually run part of the code client side? Our setup does everything server side in C#, F# and R and then just spits out vanilla Excel files without macros.If there is a need for interactivity could it maybe just be replaced by http calls to the server to avoid code duplication?

Thanks for the responses guys. That is all very useful.I am happy to hear that others have done similar things. There is a little need for interactivity, in that sometimes PMs/traders want to grab the same analysis for some point in the past, or for a subset of the data. However: I have never been happy with Excel as a platfom for doing this and I am not the only one. So there is a general move for the interactive stuff to be web delivered anyway.Therefore I think I will look into going 'all the way' and have the server generate the report files as per Hansi's comment. In a way this is clearly the best strategy, however I was made to feel some hesitation because developers I have worked with in the past (not in finance) have been of the opiniion that a database/server just serves data, rather than applies 'application logic' and that this should be handled by client side applications.I think this is not quite right in this context though because:- these servers are now rather powerful, and in our case computer resources and memory/storage are not an issue at all (i.e. compute resources per head are 'over-engineered').- finance is different from telecoms!- Excel is dangerous and would never have been used client side by any of the developers I refer too (there would be stand alone applications or web front ends specifically developed)Thanks for the pointers I really appreciate it.

What about making a client-side version of (part of) your server library, but keeping it in Python and exposing to Excel via an add-in?Your add-in might have some UDF functions that give direct data-access from worksheet formulae, or return a 'handle' of some sort that are like object references, which can be used or dereferenced from other functions.Making your back-end calculations and 'one truth' version of the data available to Excel is a big win. And if you are able to use the same Python code on both sides, you skip the VBA / Python duplication.For integrating Python with Excel there are various options, including PyXLL (better for UDFs) and ExcelPython (free, but integrates through VBA).-GovertExcel-DNA - Free and easy .NET for Excel

QuoteOriginally posted by: GovertWhat about making a client-side version of (part of) your server library, but keeping it in Python and exposing to Excel via an add-in?Your add-in might have some UDF functions that give direct data-access from worksheet formulae, or return a 'handle' of some sort that are like object references, which can be used or dereferenced from other functions.Making your back-end calculations and 'one truth' version of the data available to Excel is a big win. And if you are able to use the same Python code on both sides, you skip the VBA / Python duplication.For integrating Python with Excel there are various options, including PyXLL (better for UDFs) and ExcelPython (free, but integrates through VBA).-GovertExcel-DNA - Free and easy .NET for ExcelThat is extremely helpful, thanks I will check those libraries out. Best wishes,NG

Shorting Oasis' comeback concerts?

Shorting Oasis' comeback concerts?

Cuch has his doubts about Oasis, and The Guardian doesn't mince words. All we need now is for The Economist to pour cold water on the reunion. Then Oasis has a guaranteed blockbuster of a tour!

I’m going to see ELO in a couple of weeks, who also happens to be the first band I saw live, 40+ years ago.

I’m going to see ELO in a couple of weeks, who also happens to be the first band I saw live, 40+ years ago.

Great group.

Shorting Oasis' comeback concerts?

Cuch has his doubts about Oasis, and The Guardian doesn't mince words. All we need now is for The Economist to pour cold water on the reunion. Then Oasis has a guaranteed blockbuster of a tour!

I stick with what Grace Slick said:  "All rock-and-rollers over the age of 50 look stupid and should retire."

Fortunately for I hated Oasis with a passion, 1993-1995 were good years for David Sylvian and Robert Fripp were touring, then King Crimson did another project. So I had no need to get involved in the Oasis/Blur fence thing, thank goodness. 

If anything we should be shorting certain hotel chains and their "technical glitches" for summer 2025.  

Oasis gigs: ‘Technical error’ led to cancellation of Manchester hotel bookings, says Dalata

https://www.irishtimes.com/business/202 ... ys-dalata/