This is a guest post from our friends at Optalitix. Learn how to turn spreadsheets into APIs at optalitix.com/!
It's common in financial services and other industries to use spreadsheet-based calculations to represent real-world situations. These calculation spreadsheets are known as models. They require the user to open the spreadsheet and enter data into input cells, and then find the results they want in the output cells. While spreadsheet models are useful, they are difficult to use in a shared context. Users must use spreadsheet applications such as Excel and Google Sheets to interact with the model, and it is difficult to keep track of changes and versions. For this reason, it is often useful to convert spreadsheet models into software applications that consumers of the model can use without having to open a spreadsheet.
Up until now, it has been necessary to re-implement the logic of the spreadsheet in code in order to use the model in an app. But unfortunately, this can take a long time. In this blog post, we will demonstrate a better way to convert your spreadsheet models into usable application software with Optalitix Models and Retool.
To see how Optalitix and Retool can work together to turn a spreadsheet into an app, we'll start with a loan calculator model available from Microsoft Office templates as an Excel spreadsheet. This uses loan amount, interest rate, and loan period to determine the monthly repayment, interest cost, and total repayment value for the loan.
Optalitix Models converts spreadsheet models into APIs. It features a friendly user interface to select the input cells and output cells of the model. This includes support for tables. In the example of a loan calculation model, the input cells include the amount of the loan and the loan term. The output cells include the monthly repayments. You could have the interest and principal paid each month as a table output.
To convert a spreadsheet model into an API using Optalitix Models, you first login to the Optalitix Models portal and upload the spreadsheet. Once you have done that, your spreadsheet is shown in the browser, along with the tools to select the cells that inputs and outputs by right clicking on your mouse (see diagram below).
In the Loan Calculator example, you would select “loan amount” and “term” as input cells, and “monthly repayment” as an output cell. You may also select the repayment table as an output table.
0:00/1×
Once you have made these selections, Optalitix Models will convert your loan calculation model into an API without needing to recode your spreadsheet. You can test the API using tools in the browser. Optalitix Models will also generate the integration documentation for your API.
Now that we have converted a spreadsheet model into an API, we can build a user interface on top of it using Retool.
Retool is a fast way to build a user interface on top of your new Optalitix API, generated from a spreadsheet model. You can make REST API requests to Optalitix, using the API key generated for your API.
In the loan calculator example, we will have Loan Amount and Loan Term as inputs. We will have Monthly Repayment as an output. And to make the app more interesting, we will have the repayment table as an output, so that we can show the data in Retool in a table and in a chart.
To link this to Optalitix Models in Retool, there are a few steps required. The first step is to set up a new resource of type “REST API” in Retool. On the REST API Resource page, you paste in the URL of the Model from the API documentation which Optalitix Models has generated, and you also set up the authentication header using the API Key.
The next step is to go back to your Retool app and create a new query in Retool using the REST API resource we have created above. We now use the documentation generated by Optalitix Models to link the inputs and outputs of the spreadsheet model with the user interface elements which you have created in Retool.
To make the app more interesting in this example we have also added a table and chart to the model. And we have configured these to display the relative amount of loan interest and principal payments in each month of the loan, grouped by year. In this way, you can see how each month you pay less interest and more principal as time goes on.
Do you have a spreadsheet that you would like to turn into an application with Retool and Optalitix Models? Click here to find out more about Optalitix Models, where you can download our product guide and get in touch with our friendly team who will help you to turn your spreadsheet into an application.
