Transcription
If you know how to use the sum function in Excel, then you know enough to automate your entire Excel workbook. In this video, I'm going to show you step by step how to use Python and Excel to automate your workbook without writing a single line of code. Even if you've never written a line of code, even if you've never used Python, even if you have no idea what Python is, if you can write formulas in Excel, then you can automate your workbook. And I'm going to show you how right now.
[Music]
Welcome to the channel. If you're new here, I'm Mike. I'm a senior finance leader. And over the last decade, I've worked in finance everywhere from brand new startups all the way to the Fortune 100. And over that time, I've helped companies automate more than 50,000 hours out of their processes. And today, I want to help you do the same so you can work less and live more. So, let's get started with the easy way to use Python and Excel.
First of all, why use Python and Excel? Because you can use Excel as the front end, the Excel you know and love, while Python and its advanced automation features powers the back end. No switching tools. Everything you need is going to run right inside your workbook in the tool you already know and love. You can automate, clean, forecast data with one click. And better still, there's no IT setup, no coding degree, no copy paste chaos. You're going to do all of this automation without writing a single line of code.
There are so many use cases for finance with Python and Excel. There's everything from data cleanup. You can merge, map, and normalize your ERP exports. Forecasting. You can build trend regression or scenario models right in Excel with Python. There's variance and KPI analysis. You can literally automate your monthly comparisons between files. There's cash flow modeling. You can calculate drivers, run sensitivity analysis, generate free cash flow statements, and there's reporting. You'll find Python is really dynamic at generating tables and charts that can automatically refresh. And they can generate them in more detail and so much faster than inserting a table in Excel ever could.
So, how do you get Python set up? Well, first of all, it's available on the current channel for business and enterprise. So, if you have a business or enterprise subscription through Work, you're going to find that that has Python ready to go. If you're in an education or family account personally, you're going to need to turn on preview mode and then it will be available. It's not going to be available by default.
Python for Excel runs in the cloud. There's no installation. You don't need to have Python locally. If for some reason you have Python locally, anything you've done to that Python environment will not impact Excel. You'll have to redo all those adjustments because this is going to be its own cloud environment. Python and Excel already has Python libraries pre-loaded. This is a group of code that lets you do different functions. Pandas, numpy, mapplot, lib, and stats models. These are all data science packages that Python's going to be able to call on to use some advanced functions without you having to recreate the code. And also, this is a secure sandbox. It's safe for corporate environments because it's going to run on your computer or on your company's secured server as part of your Office 365. So, there's no worries about data integrity or any security issues.
If you haven't already, make sure to subscribe to the channel. I post brand new videos like this every single week, and I don't want you to miss a thing.
So, here we are in Excel. I've got a data file up. This is the point of sale transaction data for the F9 Finance coffee shop. And we're going to use Python to work with this data set. Now, if you want access to this data set to follow along, as well as the Google sheet that has all of the code we're going to look at, go ahead and click the link down in the description and you can get access to all these files to follow along.
So, we're on the home tab right now. To get started with Python, you're going to go to the formulas tab. Now, this is going to look very similar if you're in Excel on the desktop or if you're in Excel on the web. It will be on the formulas tab if you have access to it. Again, I mentioned the business and enterprise plans have access to it automatically. It's just going to show up. I'm on an Office 365 business plan that I use for F9 Finance. If you're on other versions of Office 365 or if you're not using Office 365, it won't be available to you and you'll need to upgrade to access it. But here on the formulas tab, you're going to see Python is right here.
There are two ways to insert Python into a cell. First, you can click insert Python and it will automatically enable it. Once the cell turns green and says py for Python, you know it's enabled. We'll go ahead and close this out. The other way is to type equals py do the open bracket and then it's going to convert to a Python cell exactly the same way.
If you want to reset your Python, you can simply click this button. If you want to access the editor, you can simply click this button. This is going to start to populate as we work with Python in the workbook. It allows you to edit any of the Python you've loaded. And then if you want to get really fancy and start customizing how your Python environment runs, you can go to initialization. This is outside the scope of the video, but something we'll definitely cover in the future as we get more advanced using Python.
So, to get started and show you the basics of how Python works, we're going to use a code with dummy data in it just so you can try this out for the first time. You can replicate exactly what I've got on my screen and learn all of the basics of working with code in Excel. So, if you've opened the Google doc, go ahead and go to the Google doc and you're going to want to get code one. And you're going to want to copy code one from import numpy as np. That's going to be pulling in one of those libraries. And you're going to want to copy all the way down right before we get to code number two. The last line is going to be plt.show. So, copy all of that in.
Then go to cell B2. We're going to hit equals py. We're going to open that. And now it's in Python. We can paste our formula in. You'll see it all past here. You can also drag this down if you want to see more of the code. And then when you're ready to run the Python code, you click controll enter. If you hit enter, it's just going to space down in the Python editor. So, you want to hit control enter.
All right. So, we've gotten our Python run. And now you'll see py image. Okay. So, that is the cell the Python's run in, but it doesn't want to just take over our Excel workbook. So, we have to tell it how we want it to show that. So, let's go ahead and rightclick on this. So, let's go ahead and rightclick on py image. There's two options. You can go to picture in cell and hit create reference. That's going to allow what we created to expand. Or you can come down here and hit display plot over cells within the Python options. Let's go ahead and hit that.
If you haven't already, click the link in the description to join my free weekly newsletter, Finance Automation Insider. I send tips, tricks, and hacks just like this every week, totally free, and I don't want you to miss a thing. Plus, when you sign up, I'll send you a guide to 15 5-minute finance automations that you can use today. Get started on your journey to work less and live more.
So, here is our sensitivity analysis. Now, what I asked it to do is to model the rate and volume impact of how raising prices could impact the total revenue for the coffee shop. So, Python was able to not only run that entire sensitivity analysis from the dummy data that we prompted it, it was also able to generate this chart. Now, if you're watching this video, you've worked in Excel and you've built charts in Excel and you know how long something like this could take with the different shadings, the different colors, getting all of the formats, getting the revenue, getting the legend, getting this all looking so nice. And Python was able to do that in literally seconds. So, think about just from how fast that was. as we get ready to connect this to our real data, how much you can do with this and how powerful this really can be.
All right, so that's our one example. Let's go ahead and move on to our second example. So, we'll go ahead and clear that out. Now, if you're following along in the doc, go down to code number two. We're going to want to start by the import numpy as npi and we're going to go all the way down to right before prompt number three. All right. And we're going to stop again at plt.show. So, we're going to go back to B2 again. We're going to do equals py. We'll paste in our code and then we will hit control enter to run it.
Now, just like before, it's going to give us the py image. We'll rightclick and we'll go ahead and expand this with display plot over cells. Wow. And look at that. In just seconds, we got two entire charts. Now, what I asked it to do in this code was again with dummy data to look at product mix for our coffee shop. So, in the top one, you'll see product mix over time and how each type of product mix is changing. you'll see we're getting a little bit more in seasonal. So, clearly this was kind of around that Halloween time frame, you know, pumpkin spice lattes. Um, aside from that, you can see all the fluctuations and then a little bit of a change as we get towards the end of the year. And down here, you'll see the impact of the product mix on gross margin. We can expand this down and make sure we can see everything. And there's all of our months and how the gross margin is changing. So, we'll see that we actually do a little bit better as we get into the seasonal time frame. So, this seasonal bump actually helps our gross margin. Now again, we're working with dummy data, but look how fast that was. Two entire charts that would probably take you 30 minutes to get to this level of formatting in Excel set up to automatically update. And now again, once we connect it to our data, it's going to be incredibly powerful.
You'll find that Python works through a concept called data frames. This is how you input data into Python. You're able to input either an Excel table or a Power Query output. Power Query Output's great because you can already do some data transformation before Python. So, Python can just do the advanced steps. Excel is going to pass the range from the input data into the Python cell. As I'll show you, Python then is going to transform calculator forecast based on what's in that data frame. Then, when it's done, all the results will still back into an Excel table, back into a data frame where it's ready for charting or any other analysis you need to do.
All right. So now that we've worked with the dummy data to show you just how powerful this can be and that you're comfortable with pasting in the Python and running it. Now we're going to create a dataf frame and work with our own data. So to create a data frame, it's very simple. We're going to do equals py again to open up python. Now we're going to type the name we want. So I'm going to use transactions. Do note that python is case sensitive. So you have to keep everything with the correct casing. I highly recommend when you work with Python just keep everything lowercase for consistency. That's a best practice that I've learned as I work with kind of any kind of coding language. Just keep it all lowercase when you can. Transactions equals and then we're going to reference where our data set is. So I'm going to come over here. We'll select our entire transaction data set. If you're following along, you'll see up here transactions is going to reference Excel. It's going to be all of table one headers are true. And we'll go ahead and do control enter to run that.
Once it processes, it's going to go it's been busy for a little bit. It's going to pull all of the data in. When it comes back and says py dataf frame, then we know all the data is processed and our data frame is ready to use. All right. And just like that, our data frame has come back. You'll see py dataf frame. Now, if you want it to be crazy, if you rightclick on this, you go to Python output and Excel value. It's going to expand all of the data. That's going to be really important for our Python outputs. I highly recommend you don't click that on your Python inputs because what it's going to do is it's going to take that massive transaction tab and it's just going to expand it on this step. We don't need that. But you will need to know how to expand this when we do one of our examples later where we have Python create a data table for us. But for now, we can just leave this as is. And then it's time to run our code. And this is where we're going to turn to chat GPT so that you can build custom code for your data set without having to write it. Now, I mentioned we're going to do this without writing a single line of code. And this is where AI comes in. We're going to use AI to write all of our Python prompting it in plain language. And this is a framework that I use. It's based on my Spark framework. if you're familiar with that. If not, click the link to this video that'll explain everything you need to know about AI prompting, but this is a Python specific adaptation of that. We're going to start by saying act as a data scientist so chatbt knows what we expect of it. We're then going to say I need your help writing Python code for Excel. Then say I want to and this is where you'll tell it what you want it to do. I want to create this type of chart. I want to run a forecast. Whatever you want it to do. My data is in a data frame called insert the data frame here. Typically, I'll have something like transactions, point of sale, general ledger, something along those lines. And then the data looks like this. You can literally just copy and paste the first few rows of your data set so it can see what it looks like. It can see the formatting and then it can build the script based on that versus trying to tell it all the columns and the data formats you have.
Have any questions so far? Go ahead and leave them down in the comments. I read and respond to every single question and I'm more than happy to help.
All right. So now, if you scroll down to prompt number three, not code number three this time. I do have some sample code there just in case you have any issues with troubleshooting, but let's go to prompt number three. We're going to copy this in. Act as a data scientist. I need your help writing Python for Excel. I want to build a chart that analyzes the performance of three store locations by hour and how they are different from each other. My data is in a data frame called transactions. And the data is organized like this. And for that part, we simply have to go to our Excel file. We're going to come to transactions and we're just going to take I usually do like the top four or five rows and we're going to get all the headers and the data formats and just paste that right into chat GBT so it knows what we're working with. So we'll paste this into ChatGpt. There's our data and we'll send it away and see what we get back.
Now for reference, I am using Chat GBT. I am on a pro plan, but I'm not using any pro features. This is just the regular ChatGpt 5 model. for this. The only difference on the pro plan is that I've got significantly more queries than on a free plan, but everything I'm showing you is totally doable on the free plan. Again, I'm not using any pro features for the example. So, this is exactly what you can get back without spending a dime. Python and Excel's for free, and the way we're using ChatCBT5 is free right now. Nothing costs money in this example.
All right. And here we've got our code coming back. So, you'll see it gave us a little bit of an explanation, and then it's writing out all of our code for us. Look at that. All right. And then it's going to say how to use it, what you need to do with this, if you need to make any changes. You'll notice a lot of times it's going to give you optional items. You don't need to use these only if you only if you want to. But we're going to go ahead and copy that code and put it into Python.
All right. So, here we are in Excel. We're going to go equals py and then we're going to paste in our code and we'll hit control enter to run it. Now, should you have any issues with the code that chat GPTt gave you off the bat, it's really easy to give chat GPT the error you get back and say, "I got this error. Help me troubleshoot." Usually in one or two times, it's going to get it right for you. But just so you can keep following along with the video in case you do get an error and you want to come back later and work on it, I have sample code listed as code number three for this. That's going to give you the result we're looking for. I had chatbuilt in this exact prompt, so it's the output that we're looking for just in case you have any troubleshooting and want to keep following along.
All right. So now you see it says image. We're going to rightclick just like we did before. Display plot over the cells. And here is our analysis of transactions by hour. Now the chatbt prompt looks like it gave us two different options. The first one is the raw transactions over hour. And the second one is the hourly share. Now what's really interesting here is the biggest trend you notice is that lower Manhattan just falls off an absolute cliff. Now that makes sense. If you think about Manhattan, probably a lot more office workers versus Hell's Kitchen. You're going to have a lot more residents. So, Hell's Kitchen is having more traffic in the afternoon, whereas Manhattan in a business district is really just falling off a cliff. Conversely, lower Manhattan has the highest business early in the day as people are coming in to work. So, really great analysis. This is connected to our actual data set. So, think about the ability if you can pull in ERP exports, build this Python code to generate your charts, they're already pretty darn well formatted, and you can have Chat GPT tweak the colors if you need to, if you have some kind of, you know, business scheme you need. and they're ready to copy and paste right into a deck in just seconds. How cool is that?
Now, this would not be a Python example if we didn't show you how to run a forecast. So, now let's look at how we can run a forecast off of our transaction data to see how our next few months might work. Now, this is going to run off exactly the same data frame. It's still our transaction set. We don't need to change that. We just need to go back to ChatgPT and get a different prompt. So, let's do that right now. This prompt for you, if you're following along, is prompt number four. We'll go ahead and paste that in. And then I can just come back and get the exact same data. I don't need to get new data because it's the same data set. So we'll go ahead and pop that in there. Act as a data scientist. I need your help writing Python for Excel. I want to build a forecast for the next 6 months and output in a table by week. My data is in a data frame called transactions. And the data is organized like this.
All right. We're going to go ahead and send that off and see what we get back. All right. And now we've got our code coming back. So it's got all of our Python. and it's going to do our imports. Again, it has some kind of instructions for us of what it's going to do. Our whole forecast model is coming together here. All right, here's all of our code. And then here are the how to use it kind of the quick playbooks. And again, we'll see if there's anything kind of optional. No, this is all code we need. So, we'll go ahead and copy that code and we will bring it over to our Excel.
Okay, we're going to come over here. We'll type in equals py again, open our Python, paste it in, and then controll enter to run it. And again, if you have any issues, you can ask chatgpt to troubleshoot. Give it the error message you're receiving and it will tweak it for you. But I also again have code under code number four. If you want to just keep following along with the video and come back later to troubleshoot, you can use that code and it will give the example that we're looking for.
All right. So now, like I mentioned, data comes into Python from Excel in a data frame. Python also outputs in a data frame if that's what you ask for. In this case, we asked for a data table. So we can go ahead and rightclick this. We're going to go to Python output. switch to Excel value and it's going to expand into a table. And there is our table. So this is our forecast. If you're familiar with statistics at all, these are our forecasting intervals, but this is the base forecast quantity we're looking for. So this is our forecast of transactions across time. You can tell that over time we slowly are ramping up. Our business is growing. That's great news. And the data set was for January through June 2023. And this has accurately given us the other 26 weeks of the year that were not included in the file. So again, just think about how quickly we built that forecast versus what it would have taken in Excel for us to model. This opens up a whole world of possibility. If you ever run out of ideas, just ask CHBT. Say, "Hey, I have this problem that I'm working through. Is this something that Python for Excel could help with?" It will help you with that and it will help you write all of the code. So this is how you automate your entire Excel workbook without writing a single line of code.
If you've enjoyed this video, then make sure to check out my video on agent mode in Excel. This is a brand new feature coming to Excel that brings the power of Aentic AI right to your workbooks, and it can tie multiple automations like Python together. I'm going to put the link to that video right here. If you enjoyed this video, you really don't want to miss this incredible new technology in agent mode for Excel. I'll catch you over there. Until then, this is Mike signing off from F9 Finance. Cheers.
[Music]