Integrate AI into Microsoft Excel for sales forecasting
Get AI where you work
This tutorial will show you how to leverage the Peltarion Platform from Excel.
You will use a spreadsheet with historical sales data to train a deep learning model, and get predictions for future dates back into the spreadsheet.
- Target audience: Beginners
- Estimated time: Setup - 10 min | Training - 5 min | Deployment - 5 min
You will learn to
- Create a training dataset from your spreadsheet.
- Install Peltarion’s Excel add-in.
- Fill your spreadsheet with predictions from an AI model.
- Optional: Build a deep learning model, with the Sales forecasting tutorial.
Download a copy of this Excel spreadsheet containing historical sales data.
The document has two sheets (tabs).
The sheet called Dataset contains the data we’ll use to train a model.
We’ll use the Test predictions sheet later to test the add-on.
You can have a look at the data and get a feel for how different features affect the daily revenue.
For example, a shop has
0 daily revenue when it isn’t Open, but not all shops close on weekends or on a Holiday.
Export the spreadsheet as a CSV file
The data in the sheet must be organized in a way that can be used for training an AI model:
The sheet Dataset is already well organized, so you don’t have to change anything.
Save the sheet as a CSV file by clicking on File → Save As and select CSV UTF-8 from the Save as Type dropdown.
Train a model
Now is the time you would head to the Peltarion Platform to train and deploy a model from this data.
If you want, follow the Sales forecasting tutorial to learn how to do it and come back here afterward.
We have also deployed the forecasting model, so just keep reading if you prefer to see how to use that model in your spreadsheet.
Install the AI for Excel add-in
The first time we use the Excel add-in, we need to install it.
You can install the Peltarion AI for Excel add-in directly within an open Excel file:
Click on Insert → Get add-ins
Search for Peltarion
Click on the Peltarion AI for Excel result, then click on the Add button
Get AI predictions in Excel
Fill your spreadsheet with test data
Open the Test predictions sheet (tab) from your own copy of the Excel spreadsheet.
This sheet contains the same kind of input information that the model was trained on, but for dates in the future. This means that the model has never seen these exact combinations of promotion, holidays, advertisement, etc., while it was training, but it will make predictions based on what it has learned from the past data.
Feel free to edit some of the variables, or to add more rows.
Get your model’s API information
Anyone can’t just use the model that you built. To get predictions from a deployed model, you will need two pieces of information:
The URL of the model
The Token of the model, which is like a password without which the model will not run
You can get this information in the API information on the Deployment view.
If you did the Sales forecasting tutorial, copy your very own URL and Token to use a model that only you have access to!
Otherwise, you can use these to get going:
Using the Peltarion AI for Excel add-in
In your Excel spreadsheet, click on the Home button and press the AI for Excel button. A sidebar will appear on the right side to help you request predictions from your model.
To get predictions:
Press Enter credentials. In the dialog that appears, enter the URL and Token from the deployment’s API information.
Press Connect then Done.
Select the headers we want as input (C1:K1 in our case) and press From selection under the Header range input on the panel.
Select the data we want to use as input (C2:K100 in our case) and press From selection under the Data range input on the panel.
Press Call model to send the specified data to your model for predictions. The result will be inserted into the L:M columns.
You’ve learned how to blur the boundary between data science and everyday spreadsheet work. You can use the data that you have in Excel to train an AI, and get smart predictions back directly inside your cells. All of that without a single line of code!