Let's call Qarnot API from an Excel spreadsheet

In this article, we'll describe how to use Qarnot computing HPC platform through XL/VBA. The provided example performs a basic $pi$ estimation though distributed Monte-Carlo simulation.

Calling Qarnot API through VBA may look steampunk! The purpose of this post is to show that it is even possible to use Qarnot distributed computing service through Qarnot than promoting VBA. Yet, VBA is still prospering and the provided sheet can be used as a basis for the a production tool, e.g. to perform portfolio risk analysis or 3D rendering front end.

To call Qarnot API, we'll use the VBA-Web project by Tim Hall that constitutes a very good and complete REST framework for Excel. The post is based on Excel 2010 version.

The Excel sheet will drive a remote Python script performing Monte-Carlo simulations., To call Qarnot API, we'll use the VBA-Web project by Tim Hall that constitutes a very good and complete REST framework for Excel.

The VBA-Web project

Developed by Tim Hall, VBA-Web (formerly Excel-REST) makes working with complex webservices and APIs easy with VBA on Windows and Mac. It includes support for authentication, automatically converting and parsing JSON, working with cookies and headers, and much more.

In this post, we'll enrich the basic VBA-Web - Blank.xlsm VBA project to add modules and class modules enabling interaction with Qarnot API. After downloading the spreadsheet, you can have a look at the VBA project by simply hit Alt+F11.

The QarnotVBA project

Based on this "blank" spreadsheet, we designed a new QarnotVBA.xlsm spreadsheet. To use this sheet and run the example, you'll need to :

• download the mc.py script here
• insert your (A) with the key you obtained by registering at Qarnot computing in the dedicated cell
• click on the "Compute!" button (D)

The mc.py python script is a simple code that will perform Monte-Carlo simulations on each node involved in the computation. If you want more info on the script, you can refer to our other post describing distributed Monte Carlo simulations.

When launched, each node will run the mc.py python script using on of the (seed, samples) parameter values listed in the Parameters array as arguments. The result will simply be a average of all output of python mc.py seed samples commands executed on each node.

If you hit Alt+F11, you'll discover the modules and class modules we added to launch the computation:

When the "Compute!" button (D) is clicked, here is the global behaviour of the compute process and the interaction of the spreadsheet with Qarnot API:

1. It cleans the spreadsheet from previous results (F and H),
2. It creates a new QarnotClient that will interact with the API
3. It generate a temporary text parameter file (named radXXXXX.tmp) containing the data of the parameter range (E) defined in the dedicated cell (C)
4. Through Qarnot API, the QarnotClient creates a resource disk and upload both the parameter file and the mc.py script
5. The task is launched using the adreeve/python-numpy Docker container
6. Each node then run the following command: python mc.py radXXXXX.tmp ${FRAME_ID}

Once launched, the spreadsheet polls regularly the status of the task though Qarnot API. The reponse of the API is displayed in the task info range (I). Every time a frame is over and successfull, the result is downloaded from the result disk.

Of course, the progress of the task can also be monitored through Qarnot console at console.qarnot.com/tasks:

Next steps!

With Qarnot HPC services, you can go well beyond what could be described in this article. You will probably come up with many more interesting use cases. So, go ahead! Try it now, and share your feedback with the community, so that we can keep improving on this product.