One thought, though I think the solution is simple: when Researcher A goes to work on a model and needs to update code on the HTTP server to match, those changes should not go live for everyone.
Researcher A might be expected to spin up a dev instance of the HTTP server, update a URL in their Excel sheet to point to their dev instance, and go.
Alternately, there could be URL parameters to pass some kind of version indication, and Researcher A might use the same HTTP server as everyone else but set their Excel sheet to request "?v=dgentry.9.5.2018" in their URLs (for example).
Versioning might also get used to resolve some of the compatibility issues mentioned earlier today, like how many regions there are. Honestly though, I think we have other options there like named fields in JSON rather than fixed-size arrays where Excel code needs to know the definition of each column.
Possibility for integration tests of Excel: http://docs.xlwings.org/en/stable/quickstart.html
For example, a test could be:
import xlwings as xw
with xw.Book(r'/path/to/drawdown.xlsx') as workbook:
sheet1 = workbook.sheets['Sheet1']
cell_A1 = sheet1.range('A1').value
integration test using xlwings and Excel can work with some compromises:
a) on MacOS, may have to enable macros within Excel. Otherwise it prompts to Enable Macros every time the sheet is opened, and I haven't found a good way to handle this automatically.
b) on MacOS, can not have a workbook already open. Otherwise it tries to launch another instance of Excel, which raises a weird protected worksheet error.
Expecting to have tests/excel_integration_test.py with a setup() function to start a flask process on an unassigned TCP port, check whether Excel already has a workbook open and fail if it does (to survive 1.b.), and then test cases for each xlsm file which will open the file, write the URL="http://localhost:port/path" to a cell, and then check the values of a few output cells again known golden values.
I will be heading with daughter to track practice at 5:30