Hey guys,
This video will show you how to easily insert Python charts into Excel using the open-source
library xlwings. Let me demonstrate what we are going to cover
in this video. In my Excel file, I have an example dataset
that contains information about the total bill, the tip size, additional information
about the customer & the day and time of the customer's visit. Clicking this button will trigger a Python
script that will return different charts from various Python libraries. We have got here a matplotlib, seaborn, pandas
and plotly chart. Xlwings support all those libraries. However, we can use a similar technique also
to insert any other images. To demonstrate this, I have prepared a simple
Stock Dashboard. After entering the stock ticker symbol and
the period, I will hit the following button. This Python script will plot the stock data
in a candlestick chart, including the traded volumes for the respective dates and returns
a line chart showing the closing price. Ok, and without further ado, let us get started. To explain the Python code, I have created
the following Jupyter notebook, which I will also upload to my Github page. You will find the link in the description
box. First things first, we need to install the
required dependencies. As mentioned already, we will be using the
open-source library xlwings. I would encourage you to use the latest version
of xlwings to have access to the newest features. You can do that by typing pip install xlwings
–upgrade in your command prompt or terminal. Additionally, we also need the pandas library. Once we have gotten that out of the way, I
will create an empty workbook using xlwings. Through the tutorial, we will use this workbook
to plot different charts. But for now, I will minimize it. Next, I am creating a small helper function
to insert text into our workbook. We will see this function in action in just
a moment. Before plotting any chart, we need to have
some data. I will use the tips dataset from the seaborn
library, which you can also find on Github. This dataset is the same one as we have in
the Excel workbook from the intro. And speaking of the Excel workbook, I will
place our empty Excel file next to the Jupyter Notebook to see the changes after executing
the Python code. Let us see how to create a matplotlib chart
and insert it into Excel to kick things off. As the first step, I will use my little helper
function to insert the heading to cell A2. Before executing the following lines of code,
let me shortly explain what we are going to do. First, I am initializing a matplotlib figure
object and storing it in a variable called 'fig'. With this figure, I will plot a simple bar
to show the total bill amount by day. To insert the chart into Excel, we will be
using the xlwings 'picture add method'. You can perform this method on a worksheet. Inside the method, we have got different arguments. The first one will be our matplotlib figure. Next, I will give the image a name and set
'update' to true. By setting update to true, xlwings will replace
an existing picture instead of creating a new one if you rerun the code. Then I am going to define where I want to
insert the chart. In my case, I will insert it in the top left
corner of cell A4. And lastly, I will also define the height
& width. So, let me go ahead and generate the chart
first. Once done, I will insert the picture into
Excel. And that's all there is to it. The code for the following examples will be
very similar. Also, for the pandas chart, I will insert
a new heading to my worksheet. Then I am going to create the pandas chart
using the plot method on our dataframe. I will store the chart in a variable called
'ax'. The only difference to before is that we need
to get first the underlying chart figure object. We can do this by using the get figure method. Once we have the figure object, I will return
it to cell A21. So, first, let me generate the chart. And then, I will insert the chart into Excel. This technique also works with Seaborn charts. First, I insert our heading, then create a
chart before returning it into Excel. Now, the cool thing, that you can take advantage
of the power of the different plotting libraries. For example, in seaborn, you can create a
scatterplot with multiple variables in just one line of code. This chart alone gives you already lots of
insights into the data. Replicating this kind of chart in Excel might
be actually more tedious. However, it seems that not all seaborn charts
are supported in xlwings. For example, I was not able to insert a seaborn
pairplot into Excel. When I tried it out, xlwings returned a blank
chart to Excel. So, keep that in mind if you are also running
into the issue when using the seaborn library. Let us move on to one of my absolute favourite
plotting libraries in Python – Plotly. Inserting plotly charts into Excel was only
available in the paid xlwings version. However, since version 24, this feature is
now also available in the open-source version. In addition to plotly, you will also need
the Kaleido, psutil and requests library. I have those libraries already installed. As before, I will insert the heading to my
worksheet. Then I am going to generate the plotly chart
before inserting it into Excel. In general, inserting plotly charts will take
a bit more time compared to seaborn or pandas charts. But with that said, you could, of course,
take full advantage of the plotly library and create more sophisticated charts. I also added a target line in my example and
inserted an annotation to indicate that the specific date was below the target. However, those additional steps do not change
anything on how we plot the chart in Excel. As seen many times before, I am also using
the picture add method with the fig variable, which references the chart object. And this concludes the list of the supported
plotting libraries in xlwings. Yet, you are not only limited to matplotlib,
seaborn, pandas and plotly charts. Many visualization libraries are allowing
you to export charts as an image. In that case, you could insert the picture
as we have seen before. To demonstrate this, I am going to use the
mplfinace module to plot stock data. Additionally, I am also going to import the
yfinance module to retrieve stock prices. After importing the libraries, I will define
the output path, where I will save the image. Then I am retrieving the Tesla stock prices
for July 2021 before creating the candlestick chart. The mplfinance library allows us also to export
the chart as a png file. After executing this cell, we should have
our candlestick in the output directory. Once I have the image, I can go ahead and
insert it into our Excel workbook. And last but not least, I will also plot the
closing price and return it to Excel. If you would like me to make a separate tutorial
on the mplfinance library, then let me know in the comments, and I will be happy to create
a dedicated video on this. As the last step, I will save and close the
workbook. If this is the only workbook open, I also
quit the Excel instance. And as expected, in my output directory, we
will find the Excel file we just have created. If you want, you could also execute the Python
code directly from Excel, as shown at the beginning of this video. To do that, I will head over to my command
prompt and type "xlwings quickstart", followed by Project name and the argument standalone. After hitting enter, xlwings will initialize
a quickstart project in the directory from where you ran this command. In my case, I will find the project on my
Desktop. If you open this folder, you will find an
Excel and Python file. Let me open both files and place them next
to each other. By the way, I have already created an in-depth
tutorial on how to combine Excel & Python using the xlwings library. I will link that video to the info card above. As the next step, I am going to insert a button
onto my worksheet. Therefore, head over to the developer tab. If this tab is not visible, right-click on
the ribbon, select developer and hit ok. From here, I will insert a button and assign
it to the sample call macro, which xlwings has inserted into this workbook. If you click this button, you should see "Hello
xlwings" in cell A1. To demonstrate how we can now insert a chart
to Excel, I will create some dummy sales figures using Excel's "randbetween" function. In the Python file, I will now convert this
data into a pandas dataframe. In my case, the data starts from cell A1. In the options, I will specify the converter
as a pandas dataframe. Additionally, I am going to set the index
to false. To return all the data and not only cell A1,
you can set 'expand' to 'table'. In that case, xlwings will use the used range
starting from cell A1. Once we have the dataframe, I will use pandas
to plot a simple bar chart. As seen before, when using pandas, we first
need to get the figure object before inserting it into the pictures add method. I will name it 'barchart', set update to true
and return the chart to cell D6. Before executing the script, I also need to
import pandas as pd. Ok, and that is all there is to it. After hitting the button, we will see our
simple bar chart. I will also upload the workbook which I have
shown you at the beginning of this video. Feel free to have a look at the code. Basically, I just took the code from the Jupyter
Notebook and placed it into a Python file. To structure the code, I have declared separate
functions for the different charts. Ok, guys, and that is it for this tutorial. As always, if you have any questions, let
me know in the comments. Thanks for watching and see you next time.
