Using API calls with VBA and Python to Read and Write to a Database

Using API calls with VBA and Python to write a database is actually a pretty decent architecture. Here, I would like to say that “It takes the best of the both worlds”, but the truth is that it takes the best from Python and somehow through a lot of “magic” it makes the VBA code works as well.

This is how the architecture looks like:

So, we obviously have 3 parts of the architecture:

  • Excel with VBA, where the user writes the data and makes GET and POST api calls;
  • Python with Flask, responsible to run a server and handle these api calls in a way to write these to a DB;
  • A DB, which is supposed to store these calls;

Excel with VBA

The first part of the architecture consists of worksheets and VBA code. The worksheets have the input in a table:

This table is uploaded to the database with a POST api call, through the function MainUpload:

Well, obviously the function above hopes that there is a running server at and calling a POST to it would do something. The function GenerateJson()  makes JSON, based on the table column names and the i-th value.

Additionally, there is a function called MainDownload() , which looks like this:

It uses the JsonConvertor class from vba-tools, which actually works pretty decent (thumbs up for the documentation, Tim). What it does is to take the http.responseText and to write it to a worksheet, which has been cleared previously with tblDb.Cells.Clear. So far so good.

Python + Flask, running a server and serving the API

Here the story is a bit easier. Somehow, Python is used to run servers and make API calls. Thus, in general we are having plenty of libraries and two files –  and I hope that it is easier to understand which one makes what as these two are really small. Anyway, run  to start the local server.


For the database part I have left 2 things – the SQL, used to create the table football_ranking and the, which shows the path to the Flask app and the DB.

At the end conda env export > requirements_py37.yml  would export all the libraries and packages, used for the project.

Enjoy everything in Git –!

Tagged with: , , , , , , , ,