Transcription
What is going on guys? Welcome back. In this video, we're going to learn how to call Python scripts from Excel using Visual Basics. So let us get right into it.
[Music]
Now, let me briefly show you what we're going to end up with here. We're going to have an Excel sheet like this one with some data in it. So, different items with different prices and weights that belong to different categories. And then we're going to have a button that does something. So, this "Start Visualization" button here is going to call a Python script using Visual Basics. So, we're going to execute a Python script by clicking on a button inside of our Excel sheet. So, I'm going to click on this and you can see it opens up a terminal or a command line. We can minimize that and you can see here the first thing is a histogram of the price. I didn't add any labels or titles, so you cannot see what this is about, but this is a histogram of the price column. We also have here a bar chart of the mean prices per category. So, the furniture mean price, the office mean price, and the tech mean price. And then finally, we also have a simple correlation heat map showing how the different features relate to one another or the different values and columns relate to one another. This is just one example of what you can do with that for this video. Know, you can do whatever you want. The main focus is on connecting Excel to Python. So, having a button that calls a Python script. This is what we're going to learn about today.
All right, so let us start by writing the Python script. This is going to be completely independent from Excel. It's going to work with the Excel file, but it's not going to interact with Excel. So, we're not going to call Excel, we're not going to call any APIs. We're just going to take a file, analyze it, and visualize the values. So, all we need to do later on is we need to use Visual Basic inside of Excel to call this script. So, Excel is going to call the script. The script itself is not going to engage with Excel. So, it's going to be a pretty simple data visualization. We're going to import pandas as PD. We're going to import matplotlib.pyplot as plt. We're going to also import seaborn as sns. And if you don't have these libraries, you need to open up the command line and you need to say `pip install pandas matplotlib seaborn`.
So, once we have that, we can just load the data. So, we're going to say `pd.read_excel` and we need to provide an Excel file here. Now, we don't have an Excel file yet, so let's go ahead and create one. This is the one from the preview. So, let's go ahead, create a new one and save it to the development directory that we're working in. I'm going to call this `my_data` and we're going to just add some simple values. We're going to add some items here. We're going to have some prices. We're going to have some uh weights and we're going to have the categories. The items are going to be laptop, book, I don't know, headphones, pen, cup, bottle, chair, paper, fruit. I don't know, something, whatever you want to put in there. The prices are going to be 1000, 200, 40, 80, 12, 7, 4, 80, 5, and 3. And the weights, I don't know if they're realistic or not. I'm just going to make something up. Uh, not 0.22 kilograms for the laptop, for the book maybe 0.4, for the headphones maybe 0.3, for the pen maybe 0.1, but a cup maybe 0.4, for the bottle maybe 0.8, for the chair maybe I don't know 2.3, for the paper if it's a lot of paper 1.5, and for the fruit I don't know 0.2. Then we have the categories: Tech, Office, Tech, Office. This is maybe kitchen. There's maybe also kitchen. Then we have uh, let's say this is Office. And then let's say paper is Office and this is Kitchen.
So, we save that. We go into our Python script and we load this data by loading the Excel file with `pd.read_excel` and then `my_data.xlsx`. And then we can just print the data to see that this works. And what we do in this script doesn't really matter too much. The focus is more on calling the script from Excel using Visual Basic. But we're going to do just some very basic visualization. So, we're going to do stuff like `data.prices.hist()` and we're going to get a histogram of the prices and I'm going to show that. Then we're going to also say `plt.bar()` for a simple bar plot. For that, we're going to group first. So, we're going to say `groups_data = data.groupby(by='categories').mean()`. We're going to group by the categories and we're going to take the mean of all the other features as the aggregation method here. And with this `group_data`, we're going to just plot the index against the mean prices. So, that's the basic idea here. We can also show this. So, let's see if that works already. We should get a histogram like this and we should get a bar chart like this. That's great.
And finally, let's do something more fancy. Let's say that we want to one-hot encode categories. So, one-hot encoding basically means you take the categories and instead of having the labels like kitchen and office and so on, you have individual features: kitchen, office, and uh, what was the other one? Kitchen, office, and I forgot it, come on, uh, Tech. So, you have these three features and then you set them to zero or one indicating if that item belongs to that category or not. So, what we can do here is we can say `one_hot_features = pd.get_dummies(data.categories)`. `get_dummies` is basically one-hot encoding in pandas. And we're going to say `data = data.join(one_hot_features)`. And then we're going to drop the categories features. So, `data.drop('categories', axis=1, inplace=True)`. And then with that, we want to plot a heat map of the correlation. So, we want to see how do the individual features correlate. `data.corr().style.background_gradient(cmap='YlGnBu').set_annotations(True)`. The annotations are going to be set to true so that we can see the actual numbers. We're going to choose the yellow-green-blue color map and we're going to `plt.show()` the results. And that should be our Python script. So, this is what we're going to call when we click on the button in Excel. This shall happen. We should see this histogram, this bar chart, and the simple correlation matrix. That's the goal.
Now, we need to integrate this into Excel. Now, in order to be able to call this Python script by clicking a button inside of Excel, now we need to first enable the developer tab in order to be able to write a macro using Visual Basic in Excel. For that, we're going to click on File, we're going to click on Options, and then we'll go down to Customize Ribbon. And on the right side, you should see this Developer checkbox here. By default, it's unchecked. You want to check it. You want to click on OK, and then you should be able to use this Developer tab here. Then we click on this Developer tab and then we also click on Visual Basic here, which is going to open up this window here. And all we need to do now is we need to go to Insert, Module. And here we need to write now our module, our basic logic for calling the Python script. And the basic idea is we just want to get a shell and we want to execute the Python script with the Python executable. So, all we need to do here is we need to create a function. And in Visual Basic, in order to create a function, you need to write `Sub`. And uh, I don't know if I can increase the font size here. I don't think that I know how to do that. So, for this, maybe you will have to accept that the font size is not too large. Maybe you can zoom in. But essentially, we're just using the `Sub` keyword. So, `Sub PerformDataVisualization()`. Then parentheses. And in here, we define what happens. So, it automatically generates an `End Sub`. This is basically our function. And in here, we say now that we want to have a Visual Basic shell. So, I'm going to call it `vbaShell`. So, `Dim vbaShell As Object`. This basically creates this object. We're going to say that this `vbaShell` we're going to set it. So, we're going to say `Set vbaShell = CreateObject("WScript.Shell")`. So, basically, we're using just a shell and then we run a command from that shell. So, we say `vbaShell.Run`. And then we use three quotation marks to specify the file path. So, we specify the uh, the path to the Python executable. So, you can open up your command line, you can say `where python`. And then you can see, okay, in this case, that is my path to the Python executable. So, I just copy that. I can paste it here. So, you can see `C:\Users\Flory\AppData\Local\Programs\Python\Python39\python.exe`. Another three quotation marks, then an ampersand to combine it with the next command or with the next um argument. And this is going to be the path to our Python script. So, our Python script, we just right-click it, we uh, open it up in, or actually, we can copy the path or reference the absolute path here. It's going to be copied. We go back into Excel, we paste it in here. Another three quotation marks. And that's basically it.
So, we save this now. Um, and the important thing now is that our file is an XLSX file. We want to have um a macro-enabled file. So, we can click here on "Yes" to just save it as an XLSX file now, but we're going to change this now here in a second to an XLSM file for a macro-enabled file. Um, and what we're going to do now is we're going to run this. So, let's see if that actually works. This calls the Python EXE and this calls the visualization. So, that seems to work. But I think we're going to have to change one thing about the script here in a second. We're going to save this for now. So, we close this window here. And in order to now create a button, we just go up here, we say Insert, uh, we get a shape here. So, for example, this one here. I'm going to draw this shape here and go with, I'm going to give it the name or the text "Start Visualization". And I'm going to center the text here. I'm going to increase the font size. And all we need to do now is we need to right-click this and we need to say "Assign Macro". And then we can assign this `PerformDataVisualization` macro. So, I don't think that it makes a difference which one I choose. Let's see. I'm going to save this now. And I'm actually going to save this as a macro-enabled workbook. So, I'm going to save this. There you go. And of course, we need to go into Python here and we need to specify that we want to call the XLSM, not the, or we want to load the XLSM and not the XLSX file anymore. So, this should still work. Let's just run the Python script. It still works with this file as well.
So, we can go into Excel now and we can try to click on that button. It opens up the command line and it starts the visualization. Now, this works when the file is in the same directory. It is recommended to specify the full path to this file as well. So, we could go ahead, we could say "Copy Path", "Reference Absolute Path", and then replace it here. Of course, when we use backslashes, we need to use two backslashes because otherwise it's going to be treated as an escape character. So, we just specify here `\\` instead of just `\`. But this then gives us the full file path, so we can't go wrong with that, even if the file is somewhere else. So, let's go ahead and um, do this one more time. So, I'm clicking on this button here. It opens up the Python script and then it starts the visualization. And the good thing now is I should be able to change the data and it's going to do, uh, it's going to update this. So, if I say "very expensive kitchen item" and I say this is $6,000 and it weighs, I don't know, 70 kilograms and it's part of kitchen, then this should now be included in the new visualization. So, we should see that the mean price of the kitchen should be way higher, as you can see, this is the case. Uh, so you can see I can just update the data. Oh, sorry about that. I can just update the data and then cause my visualization to happen. And of course, you can connect this to whatever you want. You can connect this to an FTP script that takes your data, uploads it somewhere. You can do some requests, load some data, whatever you want to do, you can do it. Um, you're just calling a Python script, and what that Python script does is up to you.
So, that's it for today's video. I hope you enjoyed it and hope you learned something. If so, let me know by hitting a like button and leaving a comment in the comment section down below. And of course, don't forget to subscribe to this channel and hit the notification bell to not miss a single future video for free. Other than that, thank you much for watching. See you in the next video. And bye.
[Music]