The Easiest Way to Automate Excel with Python

If you are looking for a way to switch from VBA to Python for Excel Automation this video is for you. Now if you search online, for example on Google or here on YouTube you will see a lot of people will tell you to use Pandas or OpenPyXL. Now those packages of course are super handy but when it comes to Excel Automation they are a little bit limited. Of course it also depends on your specific use case. But in this video I want to show you a Python package that I think is a true VBA replacement. It works by talking to Excel through the COM interface just like VBA does. That said, don't worry it will also work on Mac OS. So the package is called xlwings. And the good news is the syntax is very close to VBA. Now and just to be clear this video is not to say that Python is better than VBA. I personally still use VBA a lot but in this video I just want to show you how you can transition from VBA to Python when it comes to Excel Automation. So with that said let us dive in. Alright, now assuming you already have Python installed on your machine the first thing is that we want to install the xlwings package onto our machine. So I will open a new terminal. And in here you want to type pip install xlwings. And optionally if you want you can also include the upgrade flag. So in that case you are just making sure that you are always using the latest version of xlwings. So with that I will just press enter. And now xlwings will be installed on my computer. Alright, once that is done you want to run the command xlwings quickstart. And then you can give your project a name it can be anything you want. So in my case I will call it Demo Automation. And then at the end you also want to include the flag standalone. So two dashes and then standalone. And when you press enter xlwings will now create a new folder in the directory from where you ran the command. So in my case it will be here, the Excel Demo Automation folder. And inside you will now find the new project which is called Demo Automation. And inside that folder you will find two files one is a Python file and the other one is the Excel file. So first let us have a look at the spreadsheet. Now if you spreadsheet you will already notice that this is a macro enabled workbook. That is because it already includes all the VBA code which is required to execute the Python file which is also located in the same folder. But more on that later in the video. First let me add a button to our sheet. So I will go to my developer tab, go to insert and then select the button drop it onto my sheet and then I will select all the macros from this workbook. And in here as I said you will now find already all the macros which are coming with the xlwings starter project. So in our case we want to select the sample call here and then click on OK. Now when you click on this button ideally the Python file should have been executed. However, in my case as you can see I'm getting an error saying that the interpreter couldn't be found. And this just means that xlwings couldn't find my Python installation on my computer. But that is a quick fix because if you have a look down here you will find a config file and in here you can now specify the path to your Python installation. And also if you want you can also specify which environment you want to use. However, as also written here you need to remove the leading underscore. So let me do that. So I will click in here remove the leading underscore and now this setting sheet is active. So if I now go back to my sheet one and click the button again we should now see Hello xlwings in cell A1. All right. And when I click it again it should say Buy xlwings. OK. So this worked. Now what just happened when I click the button? So when you click the button a VBA code was executed and that VBA code was then executed in the Python file which we have seen in the directory. And in that Python file we have then specified that we want to write Hello or Buy xlwings into the cell A1. So to show you how everything works step by step let me open the Visual Basic Editor. So when I do that here you will see two modules and also a class module which is the dictionary. Now the dictionary class is just a drop-in replacement for the default scripting dictionary from VBA. But the beauty of this solution here from Tim Hall is that it will also work on macOS. Now this dictionary is just a helper which is then used in the main module to perform xlwings. And in here, if you scroll down a little bit here you will then see the runPython function. And as the name suggests, with this function you can now run a Python file. So this function here, the runPython function, is used in the first module. So if you have a look inside that module here you can now find the sample call which we assigned to the button. And the first line looks a little bit convoluted but basically all it does is to extract the workbook name. And then we're using that workbook name to run the main function from the Python file. And with that in mind let us actually have a look at the Python file itself. All right, here as a first step we are importing xlwings as XW. So this is exactly the module we installed at the beginning of the video. And in there you will now also find the main function which we are executing from VBA. And here within the main function

you will see that we are establishing a connection to the calling workbook. So we are using XW and we are using the book object. And in there we have a caller method. So with that, we are now getting the connection from our workbook to our Python file. And then we can do all the fun Excel automation stuff which you would also do in VBA. And the beauty here is that the syntax is actually very similar to VBA. So let me show you what I mean. So as a next step I'm defining a variable called sheet. And we can specify our sheet if we have a look into the workbook object. And from there, we will find the sheets. And then I can specify which sheet I want to manipulate. Here, you can either use a sheet name or in my case here I'm using the index. Just note in Python the first index starts at zero and not at one. All right, so once you've got the sheet we can also check for any cell values. For example, I can check if the cell value in A1 is equal to Hello, xlwings. And if that is the case I want to replace it with Bye, xlwings. However, if it's not equal to Hello, xlwings then I want to write Hello, xlwings. So this now also explains the behavior we have seen when I press the button. Because the first time I pressed the button nothing was in cell A1. So that's the reason that wrote Hello, xlwings into the cell A1. However, the second time I pressed the button, Hello xlwings was already written in the cell A1. And that's the reason then this line got executed. All right, now this is, of course, just a really basic getting started example. But to show you a little bit more practical use case of xlwings let me delete all this code here and grab some code from my other screen and paste it right in here. Now with this code we are creating a little stock dashboard right inside Excel. Now, full disclosure here I didn't write the code myself. I basically just asked Claude to write the code for me. And this is also the beauty of Python. Because most of those large language models, so JQBT, Claude, Gemini and so on and so forth they are all really good when it comes to writing Python code. So for example, you can just ask them using xlwings I want to create a stock dashboard. These are my requirements. Please give me the code. But anyways, in this video I will also explain the code here step by step. So as a first step we are again importing xlwings as XW. And now we are also importing a couple of other modules. And this is really where Python shines. Because in Python, there's basically a module for everything you want to do. For example, if you want to retrieve some stock data you can use the yfinance package which I also did here. Or if you want to do some plotting so creating some charts, for example there are many different modules out there. One of them is, for example, matplotlib which I'm also importing here as a module. And the last but not least there are also some built-in Python modules which already come with the core Python installation. And one of them, it's datetime. So with datetime, as the name suggests you can manipulate some dates. And in our case we will just insert a timestamp later on into our sheet. So with those inputs in place let us have a look at the main function. And in here, you will see that I'm first, again, establishing a connection to the calling workbook. After that, I'm storing the Excel application itself in a variable called app. And this can be super useful. So as you know when you're using VBA you can do all kinds of automation to the workbook, to the worksheet, to the range, to the cells, and so on and so forth. But if you want you can also interact with the Excel application itself. For example, you can set the screen updating to false, set the calculation method to manual, and so on and so forth. And with xlwings we can also do that. Now, in our case I want to display a message box. And this is what I'm doing here. So we are using the Excel app. And in there, instead of message box it's called alert. And I want to show this message box if the named range ticker is empty. So here, as you can see I'm looking for the named range ticker inside my workbook. And I'm storing this in a variable. So this one is, for example, empty. And then I want to show a message box to the user. Or if the named range doesn't exist, again then I also want to show a message box. Now, once I retrieve the ticker symbol from Excel I want to create a new sheet, so the stock dashboard. And in there I want to display all the stock information and also my chart. So that means, as a next step I want to create the new sheet. However, I also want to start fresh whenever I click the button again. So that is the reason, first I'm deleting any sheet which has the name stock dashboard. And here, there are, of course different approaches on how you can do that. The first approach might be, first to check if the worksheet already exists. And if it doesn't exist then you can create it. But in Python, typically I go with the principle I better ask for forgiveness instead of permission. So that basically means I'm just using here and try and accept block. So if there's an error I will just handle the error. And this is exactly what I'm doing here. So I'm just trying to delete it. However, if I'm running it for the first time we don't have the stock dashboard sheet. So this method will fail. But anyways, I will just continue then with my script. So this just means I can now add my worksheet. So again, I can access my workbook. And within the workbook I have the sheets. And from the sheets I've got different methods. One of them is the add method which will add a sheet to my current workbook. And in here, I can define the name.

And I can also specify where to insert it. So in this case it will be after my first worksheet. All right. And once I have that I will activate my new sheet. Now in here, once again you can see the power of Python. Because with just two lines of code we are retrieving the stock information for any given ticker symbol. In this case, I want to get the historical stock data for one month. Now, if this data is empty, for example if the user inputted an incorrect stock ticker symbol, then again I want to show a message box. And additionally, with the yfinance package we can also get some company information. So here, I'm also retrieving that information and storing it in a variable called info. All right. And once I've got all my information, so the stock information also the company information then I'm just inserting all of that back into Excel. And this is pretty straightforward and very similar like you would do it also in VBA. So for example, here I'm using my active sheet. And in cell A1 I want to insert my heading which is stock analysis dashboard. And then if you want you can also style it a little bit. For example, making the font a little bit bigger making it bold and also giving it a different color. All right. And now I'm doing this also for the other information. So for the stock information I'm just placing everything in the different cells here. And then I'm also calculating some metrics and also inserting that back into Excel. And then finally, we're also getting our historical stock data. So for the last month. So again, here, I will first create a little header and then display all the stock information into an Excel table. All right. After that, I'm just auto-fitting the columns. And then last but not least I'm also creating a plot of this map plot lib. So once you've created the plot you can also insert that chart as an image directly into Excel. So this is what I'm doing here. And then last but not least I will also add a timestamp. So again, here, we are using the datetime module. And then also displaying a message box for the user. So in this case, it just says dashboard has been successfully created. And then also showing the current price and the 30-day change. All right. And that is basically it. So the whole code here is around about 190 lines. And when we go back to Excel we can try to run it now. So if I click the button now I actually now should get an error message because I haven't defined the named range ticker. So as you can see here. So let me quickly do this. So all right. So I just did some formatting here. And now all I need to do is to click into this cell here and give it a name. So this should be now ticker. And when I press enter I now have my named range. That means we can test it out. So let's try to get the information for Microsoft, for example. So when I press this button now we should now see a new sheet. And then on the left we can see some KPIs, for example, the current price, the 30-day change rate, the high and the low. Then next to it we've got the historical data for the last 30 days. And on the very right we can see the chart. So we've got here the price with the high and the low and also the trading volume down here. All right. And that is how easy it is to create some Excel automation using Python. And of course, we can also try it with another stock. So let's take, for example, NVIDIA. And when I press the button again we will now see our message box again. And all the other information got updated now. All right. Now, one big downside is when you are using Python for Excel, you, of course also need to have Python installed on your machine. And of course, also when you're trying to build a solution for your co-workers, for example they also need to have Python installed on their machine. Now, there are also some solutions for that, for example, Excel Ring Server. But the setup is a little bit technical and I won't cover it in this video. However, what I will do is to show you an alternative which is called Excel Ring Slide. So Excel Ring Slide is an Excelitin which you can install with a couple of clicks right inside Excel. And with Excel Ring Slide you can run Python code even if you don't have Python installed on your machine. So let me show you what I mean. So first to install the add-in go to your home tab and then click on add-ins. Here you want to search for Excel Ring Slide. Now, in my case I already have it installed as you can see here. But in your case it should show up somewhere here and then just click the add button. When you do that you will have a new icon here in your home tab which is called Excel Rings. So when you click on it a new task pane will open. Now, and inside this task pane you can now write all your Python code. And once again, this Python code will also run even if you don't have Python installed on your machine. This works because Excel Rings uses Pyodai and WebAssembly to run Python in a sandbox environment in your browser. I know that sounds a little bit wild, but rest assured all your data stays on your computer. So none of your data ever leaves your computer. Everything runs locally on your machine. And to show you that it works let me just click on the Hello World button here. So when I do this we will see Hello World in the cell A1. And we can also check out the code which is right here.

So here we are defining a function which is called Hello World. And inside, you will see that the syntax is exactly the same as in Excel Rings. So we are getting the current workbook and the current sheet. And within the sheet I want to get the cell A1. And with that cell I want to change the value and the color. And again, this is, of course, a really simple example. But if you want you can also do more advanced analysis using Excel Rings Lite. So to show you that let me select here the CMON example and then click on this button. So here in the code as you can see we are first getting some data which is stored in a CSV file. And the CSV file is located on GitHub. Then again, we're adding a new sheet to our workbook and inserting a header. So here we are in the Pinguin dataset which has a bold font, a bigger size, a different color. And we're using here the good old Comic Sans font. All right, and after that we are displaying all the data which is coming from a CSV file directly into an Excel table. And then we're also creating a join plot with just one line of code which you can see here. Now, if you want to learn more about Excel Rings Lite I've got some good news for you because I already have a couple of tutorials here on my channel. And I will also make sure to link those videos directly in the description box below. Also, if you want to learn more about Excel Rings in general, so the syntax, I've also got a couple of more beginner-friendly videos because in this video I was going pretty fast on the code. But in those other videos I'm explaining a little bit more into depth on how the syntax works in Excel Rings. So if you're interested in that, then again, check out the links in the description box below. All right, and with that said if you now got any questions just drop them in the comments below and I will try to answer them. And as always, thanks for watching and I will see you in the next video.