How to Run Python in an Open Excel Workbook (EASY) | Combine Excel & Python | xlwings Tutorial

hey guys in this tutorial i'm going to show you how you can run python in an open excel workbook in particular i will cover the scenario where we have a python script and you want to return the result in excel i will show you how you can easily combine excel and python by using the free open source library xl wings axle wings can leverage python packages to white tools with python instead of vba at the end of this tutorial i will show you some practical examples those examples will include plotting and updating stock market information as well as interacting with apis and return the result to excel and even creating a pdf stock report however in this tutorial i will not cover how to execute a python script from excel i will make a separate video on this which you will find in the description box down below once i have released it and without further ado let us get started for the sake of explanation i will create two data sets for this i will be using the pandas library as well as the pandas data reader to retrieve stock market data if you want to follow along and you do not have those libraries installed yet you could run the following cell the libraries will be then installed in the current instance of python which launch the jupyter notebook next i will import the libraries i will not go into the detail for the following function as we will focus on axle wings in this tutorial essentially this function will return a data frame which contains the adjusted close price in december 2020 for any given ticker symbol for this example i'm retrieving the adjusted close price for tesla the second data set is from the seabourn library which contains the miles per gallon information for different vehicles having those two data sets let's see how we can combine the power of excel and python to manipulate the data by using axle wings you can install axle wings by typing pip install axle wings if you're using anaconda excel wings comes already pre-installed however i highly recommend you to update the excel wings library to have access to the latest features to update the library you could use the upgrade command once installed i will import excel wings as xw in the first section i'm going to show you a very quick and easy way on how to inspect any data frame directly in excel as you can see pandas does not print out all the rows of the data frame you could now change this behavior in panas to display more rows or columns however you could also use the xw view method to inspect the data set directly in excel running the cell will open up a new workbook the data frame is automatically converted to an excel table you could now go ahead and easily filter the columns or scroll for all the available rows needless to say you could inspect the dataset also with various pandas functions but especially when dealing with smaller data sets i think this is a great way to explore your data i will also open up the stock market data set in excel with excel wings version 0.22 there's also a new function available to directly load data from excel to a panda's data frame if you select the data go back to your python script and run the excel wings load method it will convert the selection into a pandas data frame alright so this was the quick and easy way coming up next i will show you a little bit more explicit way on how to interact with excel the first thing you want to do is to build a connection between python and an excel workbook to get a connection to an actual workbook there are three scenarios in the first scenario you want to run your code in a new workbook so excel rings should create a new workbook and execute your python code in there the second case is that you have already an unsafe workbook open in which you want to run python and last but not least you have already a saved workbook which you want to manipulate let us go ahead and see how to connect to a new workbook i will store the connection to that workbook in a variable called wb to initiate a new workbook you can type xw.book running the cell will open up a new workbook now we just need to specify the worksheet we want to manipulate to specify the sheet you could either type the name so like sheet1 or use zero base indexing for me i'm going to interact with the first worksheet so the index is zero once axelrinx knows the workbook and worksheet i will further specify the cell we want to interact with in this example cell a1 the value should be equal to our small data frame of ticker symbols which we have created earlier and if you know vba you should recognize the familiar syntax here after running the cell we can see our values starting from cell a1 in the next scenario i will connect to an open but unsaved workbook therefore i will open up a new excel workbook it is very similar to the code before you just need to define the workbook name which you could find over here like before i will declare the sheet variable which will be equal to the first worksheet this time i will export the stock market data frame to excel which we have created earlier to save the workbook you could type w.save followed by the name of the workbook we can close the workbook with the following command and i will also close the workbook we have created earlier if we have a look in the directory of this jupyter notebook we will find our stock.xlsx workbook this workbook will serve as an example on how to connect to a saved workbook to open up this workbook i will first define the file path by using the built-in python library pair flip as a next step i am passing over the file path to the excel wingsbook method running the cell will open up the workbook if the workbook would have been already open excel wings will not reopen it instead it will just establish a connection to the workbook in the background as seen before i will specify the worksheet this time i'm using the worksheet name instead of the index after connecting python and an excel workbook let us discover some excel wings functionalities keep in mind excel wings has a lot more to offer i will just show you some of my favorite methods here all the available methods can be found in the official documentation i will now go ahead and add another worksheet called basic examples reading cell values is very similar to what we have seen so far in this case i'm reading in only one cell however you could also read a cell range excel wings will convert the values to a python list all available excel formulas could be also inserted via axle wings here i will keep it simple and insert this formula in cell b1 instead of hard coded cell ranges like a1 b1 etc i often use the name ranges it gives you much more flexibility if you move the cells around as an example i will create a list of month names by using the excel autocompletion function after highlighting the cells i can provide a name for this particular cell range i will call mine months back in excel wings instead of specifying the range between d3 and d13 i could use the name range months to get the values if we print this out it will return the months in a list likewise we could also set a name range from axle wings after running this cell we can see that excel has now the new name range called ticker list one of my absolute favorite features in excel wings is the ability to convert excel data into a panda's data frame to give you an idea on how easy it is i will shortly create some dummy sales and profit figures in excel once done let me break down the following line of code i'm selecting here cell d2 the command expand table translates to the operation if you would press ctrl shift down and ctrl shift white on your keyboard to select the axle range it will grab all the data without needing to specify the exact range which gives you all the flexibility as this code will also work even though you add more rows or columns the first argument of this option is the desired output of those values in my case it should return a pandas data frame by running the cell we could check our new data frame below and now you could use this dataframe to further manipulate it with the help of the pandas library like getting the average sales amount for example instead of saving this workbook as an excel file you could also convert it to a pdf back in the folder we have now our freshly created pdf file where each worksheet is on a separate page after knowing the basic operations and functions in excel wings i will show you some more practical examples for this i will be using the map.lip library i will use my helper function to retrieve the adjusted close price for tesla and store it in a data frame as the next step i'm using the built-in pandas plotting capabilities to plot the adjusted close price in a line chart excel rings allows you to directly insert multiple lip charts in excel to get the mumplot lip figure object from the pandas chart we can use the get figure method next i will add a new worksheet in this new worksheet i will insert the line chart we just have created and i think this alone is pretty cool but it's getting even better as i have set update to true we could now go back to our function and return the adjusted close price for facebook after plotting the facebook closing price we could rerun our code and it will automatically update the chart in our excel workbook if you would have set update to false excel wings would insert a new image in the workbook i think this is a super cool feature and you can already see how powerful and useful this library is just to give you some more ideas in the next example i'm using the request library to connect with an api the following function will return a useless fact after adding another worksheet i am now inserting the useless fact in cell a1 so instead of doing all the hard work in vba to get some data from an api you could leverage the power of python with all the available libraries and integrate it into excel similar to the plotting example with muppot lip axle wings can also insert and update images in excel in this example i'm using another api to retrieve a dock image and store it in the same directory as the current jupyter notebook instead of passing over the mumpot lib figure you could insert the absolute image path after running this cell it will insert the image into the worksheet as i have also said updated too the image will be updated whenever i rerun my function again in the last example we will bring all the knowledge together from what we have learned in this tutorial in the folder stock report you will find the following excel file the report shows accumulative returns draw downs daily returns as well as a monthly return heat map for any given ticker symbol currently you can see the performance of alphabet so the ticker symbol is gog let me update this to facebook the charts are generated with the help of the quonstats library which you can install by running the following cell after importing the library i will set the workbook connection to the stock report and specify the worksheet we want to manipulate in the following function i'm now getting the ticker symbol from excel which is in cell b1 once we have the ticker symbol i'm generating the performance snapshot and the monthly return heat map by using the quant stats library and store those in separate images once done i can return those images update the charts in excel and convert it to an pdf after running the cell i can see the new pdf with the information for facebook now i could just go ahead and change the ticker symbol directly in excel run my python file and i will get the updated report okay guys that's it for this tutorial i hope you can see how powerful excel wings can be so in the future i will be also creating more tutorials and examples on how to combine excel and python and as always if you have any questions or need further support just let me know in the comment sections down below thank you very much for watching and see you next time [Music] you

Channel: Coding Is Fun

Views: 42,511

Keywords: xlwings, python excel, run python in excel, execute python in excel

Id: _aPa9gZ1s0M

Length: 13min 31sec (811 seconds)

Published: Thu Feb 11 2021