Sales Forecasting in Excel with Machine Learning and Python Automation

Key Takeaways

ever wanted to get a better handle on your sales forecasting in today's video we're going to go through exactly that we'll take a look at how you can use order AI to build a no code machine learning model and how you can integrate it with excel so in today's video we're going to be covering a bunch of stuff but specifically we're going to be covering how to predict sales using machine learning and specifically we'll be using auto AI inside of Watson studio then - we're gonna take a look at how we can deploy those machine learning models so we can integrate them into other applications then last but not least we're going to take a look at how we can integrate this model with Microsoft Excel to generate automated predictions so while sales forecasting well sales forecasting is critical to businesses all around the world because ultimately sales is what defines the baseline for driving the ultimate profitability of a company so what you'll find is a lot of businesses focus on forecasting sales to work out what their environment is going to look like in the future now there's a couple of methods to approach when performing sales forecasting you can take a basic method and just use a moving average or just copy what you had from last year or you can start to get a little bit more advanced then take a look at methods like ARIMA so order aggressive integrated moving average or use deep learning and machine learning which is what we're going to be doing today now on that point what are we going to be doing today so the first thing that we're going to be doing is uploading a CSV inside of Watson studio that CSV contains our training data and it's what's going to allow us to train our machine learning model we're then going to use a regression algorithm inside of auto AI to predict our sales going forward we'll then take that same model and deploy it using what's in machine learning so this will allow us to integrate it into other applications going forward and last but not least we're going to write a Python script that allows us to automate the sales forecasting process alongside a Microsoft Excel document you ready to get into it let's do it the first thing that we're going to do is step into Watson studio so we're going to prototype our machine learning model using Watson studio and then we're going to once we build that model deploy it and then work with it with Microsoft Excel on our desktop so let's go on ahead and login and then we're going to create a new project and create an empty project and let's just call this sales forecasting with Excel then hit create and so that's gonna create our project from within our project we can load in all of our data assets and really just go from that perfect our projects being created now because we're going to be working with order AI we're going to create an order AI experiment this is going to allow us to create an automated machine learning pipeline which will then be able to deploy so let's add project and then hit order AI experiment now before we actually create an auto AI experiment we need to associate a machine learning service so we're going to need some details from this machine learning service later on as well but for now let's just associate our service so if we select associate service then here we're going to select machine learning - ze so this is one that I just created before if you don't have one already just select new select light and then go all the way down and hit create because we've already got one set up we can just select an existing one and hit select then hit reload and perfect so we've got a machine learning service now associated then what we're going to do is name our experiment so because it's to do with sales forecasting we're just going to call it sales forecasting and hit create ok so now that our experiments being created what we're going to do is upload some data so I've got all the data sets required for this inside of a github repo the link is in the description below you can grab those and work with those there's going to be two key data assets those are our training data set and our testing data set for our live production data set so the first thing that we're going to do is hit browse and then I've got my data sets on my desktop in a folder called sales forecasting now the data set that we're going to train on is called training underscore data set CSV so just select that data set and hit open and now as soon as we upload our data set order a is going to automatically pre-process our columns going to take a look at what we've got in there and understand the data types that we're working with so as soon as that's finished processing we'll be able to select the column that we want to forecast and forecast from there and you can see it's finished processing so what we just need to do is select our column and the column that we want to predict is called sales so sales is effectively our dollar sales that we were trying to predict so if we select sales you can see that the correct type of prediction has already been selected so in this case it's regression regression is all to do with predicting a continuous number and in this case sales is a continuous number so correct prediction tasks and we've also got our optimized metric which is residual mean squared error in this case ideally it's going to try to minimize the difference between a prediction and our actual value once that's done just hit run experiment to kick-off the prediction or kick up the training set perfect a run has started now what we want to do is take a look and just swap view so you can see all the stuff that order a is doing so in this case it's reading in our data set splitting it into training and testing and a holdout partition it's going to do a bunch of pre-processing a bunch of model selection and eventually what you'll get is 8 machine learning model pipelines so this is similar to the video that I did on churn where we took a look at how to predict churn using order AI now what we're going to do is let this finish training so eventually we should get 1 2 3 4 5 6 7 8 machine learning pipelines then what we'll do is we'll deploy the best machine learning pipeline and start working with it with Excel so to generate automated machine learning predictions for our sales forecast we'll be back in a sec a little longer than a few minutes later and we're back so now all about machine learning models have finished training and we can see that pipeline 4 is the best performing model so given that what we're going to do is deploy that machine learning model using Watson machine learning and then get on to our excel bit so what we need to do in order to deploy this machine learning model is just scroll on down into this corner hit save as and then choose model we're just going to leave the name in for now hit save then we can go view that model inside of our project and then what we're going to do is go to deployments so deployments allow us to create something called a REST API and a REST API allows us to integrate our machine learning model into other applications in this case we're going to use our REST API to generate predictions from our Excel document so let's create a deployment we're going to call this Excel deployment or sales forecasting and we're going to hit save okay so now what we need to do is just wait for that deployment to finish initializing and as soon as that model is finished deploying this status will shift from initializing to ready okay so our deployment status is now shifted from initializing to ready so what we can then do is step into that deployment and grab the code to start hooking this into Excel model so you can see whenever you deploy a model if you select the implementation tab you're going to get a whole bunch of code to actually start integrating this into different applications so in this case we're going to be working with the almighty Python and we're going to be using our Python code to start integrating this to our excel doc oh so what we're going to do is copy this code and we're going to create a new Python document or a Python script to allow us to call that machine learning code so let's create a new Python doc oh so I'm just going to be using vs code for this and let's just drag our folder in there so in this case I've got a folder called sales forecasting so I'm just going to be working from that and now we're going to create a new file and we'll call it Auto forecast dot PI cool so then we're going to paste our code that we just generated from our deployment in there and we'll come back to that in a second now we're going to need two things in order to proceed forward so we're going to need to generate some credentials in order to hook into our API and we're also going to need our machine learning instance ID so let's go and grab that so before we actually go and grab that actually let's create a in order to store these credentials so we're going to create a dictionary called wml credentials and inside of that dictionary we're going to have three things so we're going to have our API key we're also going to have our instance ID and we're also going to have our URL perfect so let's just put a comma there comment there and we're going to insert it here insert API key yeah insert instance yeah and we're going to insert a URL here alright so let's go and grab these details now so in order to get those details for our machine learning service we just need to hit the burger menu there and go down to our Watson services so if we open that up in a new tab we can then scroll on down to machine learning and if you remember correctly when we set up our machine learning service we know that machine learning does ed-e so I'm gonna step into that service so in this case we're just going to tap into the existing credential that we've got there so you can see it's called wdp writer let's just open that up and you can see we've got our API key we've got our instance ID and we've got our URL so let's just copy those so I'm just going to copy our API key and paste that in there then we'll grab our instance ID and we'll paste this in here and last but not least let's grab our URL and throw that in here perfect so that's our core wml credentials set up now let's start fleshing out our Python script so that we've got a little bit more structure around it right now it's just a bunch of stuff that we've thrown in there so let's select it all and tab it inwards we're going to encapsulate all of our code within a function and we're just going to call it main so this is sort of pretty standard practice when you're working with Python and then we're going to include a script so that when we run it we're going to trigger our main right our main function so this little piece of script here basically allows us to trigger off our main function when we run our Python script from the command line so so basically when we run it from the command line this is effectively going to call our main function up here now we've got to import one or a couple of libraries as well so specifically the first library that we're going to import is the Watson machine learning API client so let's go ahead and in sport that cool so that's we're importing out what's in machine learning API client from the Watson machine learning client library we've already got our credentials so let's just add some comments there so wml cred and chills and then what we're going to do is a couple of things so first up we're going to create a new instance of the client we're also going to generate an API token or an iam token and then last but not least we're going to extract ml instance to a variable alright so let's go ahead and create an instance of our client so we can do that just by creating a variable called client and then we're going to use our Watson machine-learning API class to that we're going to pass our credentials which we defined up here so that should be fine then what we're going to do is use this client to generate a token so a token is basically going to allow us to authenticate against our deployed machine-learning service so let's go ahead and do that we're going to create a variable called i.m token and to do that we'll call our client and we'll use the wml token property to extract that token and then what we're going to do is just create another variable for a machine-learning instance so we're going to call it ml instance ID equals we're going to grab it out of this particular dictionary here perfect so now what we've done is we've set up our credentials we've created a client we've got a token with extracted a machine-learning instance ID now what we're going to do is just reformat this code that or this boilerplate code that we've take of what since to do so now this particular line is fine so this is or to do without request we can delete that out of there so this is just generating our header and to our header we're going to pass our I am token which we've defined up here and our machine learning instance ID will prove defined up here this payload is quite important so this is where we actually send our data send or set up our data and up data from Excel document and you can see if we scroll all the way over to the right this is where we pass in our values so you can see there so you can see there all the way over here so array of values to be squatter another array of values to be scored so now what we need to do is grab some data from excel document so to do that we're going to be using it pandas so let's bring in pandas let's create a new comment so get data from excel document and do that so we're going to import pandas as PD then we're going to create a data frame and we're going to use the read excel method to read in our data from our excel document so if we take a look inside of our folder the document that we want to take data out of is as store sales dot xlsx file so this is basically the exact same structure that we had inside of our training data set it's just that it doesn't have our sales data so what we're going to do for that is work with just going to call the name of that file so sales what is it store sales XLS X coop that's fine and then we're just need to do a little bit of reformatting on the date so we need to just convert it to a string because by default pandas is going to read it in as a timestamp so our machine learning instance is expecting it to be a string so we're just going to do that data conversion there and we're going to fill in the values so good data science practice is to open up your document review it make sure that you're filling in the right values correctly I've already gone and taking a look the values that we're filling in a real the numeric values which we can fill with zero so we're just going to fill them with zeros and we're going to set in place equals true and then what we need to do is we need to actually grab out or push our data to this array of values here so in order to do that we're going to make our lives a little bit easier so let's scroll on over we are going to strip out that array there and we're going to call DF values so this is just going to grab the values from our data frame and then we're going to convert it to a list so when we pass this through to our machine learning service what it's expecting is a list of values so by changing it to list we're effectively transforming it into the correct format so that looks fine let's just double check what else we need to do down here so that's then going to send off our response so this is going to execute the request and we can get rid of there is two and then what we're going to do is the last thing is we're going to grab our data up we're going to put it back inside of our data frame and then we're going to dump it out into an excel dr ultimately we've got everything sort of encapsulated within one excel document so now what we're going to do is extract our predictions we're going to convert it to Jason then what we'll do is we'll store the values from that particular prediction inside of a new column called DSL so when we run this we're actually going to have as sales predictions inside of a new column called sales within Excel docker then we're going to use list comprehension to extract our data so we're effectively going to be looping through each one of our values inside of our prediction so we can do that so for x in predictions and then we're going to need to traverse through these predictions and we're just going to grab the first instance and grab values because of though the way the predictions are formatted this is going to allow us to extract each one of these values and load it directly into our data frame so let's just quickly check that and then last but not least we're just going to export it back to excel and we're going to use the to excel function or method within handles for that so we're going to call DF to excel and we're going to call it predicted sales dot xlsx if we hit save that all looks good so I'm just going to open up a new terminal window and then we're going to navigate to where our auto forecast our PI code is so we go to CD desktop CD sales forecasting and then if we take a look you can see our auto forecast our PI code is in there now if we run this successfully what you should see is a new file called predicted sales dot xlsx pop-up yep so let's try that so python and then we're going to run auto forecast dot pi and let that run so you can see that that's completed successfully no errors and you can see here that we've got our predicted sales file so if we take a look now if we open up our original store sales document you can see that we had our store we our date open promo but there was no column which included sales now if we open up our predicted sales file you can see we've got our store date and so on but we've also now got our sales prediction so all up what we've done is we've trained our model we've gone and deployed it and now we've used the Python script to automatically call it and generate our sales forecast that about wraps up this tutorial let's recap of what we did today so the first thing that we did was we built a machine learning model using Auto AI where we were able to predict and start forecasting ourselves we then took that same model and deployed it using Watson machine learning so that we could integrate it into other applications and last but not least we then wrote our Python script so that we could automate ourselves forecasting alongside our Microsoft Excel document and that about wraps up today's video thanks so much for tuning in guys hopefully you found today's video useful if you've got any questions at all be sure to drop a comment in the comments below and let me know how you went if you want to stay up to date with the videos that I'm releasing be sure to subscribe and hit that Bell and if you'd like to know anything more again just feel free to reach out to me thanks again for tuning in guys peace [Music] you