Introducing Python in Excel - Financial Analysis Dashboard using Python + Excel + ChatGPT Part 1

today I am super excited to make this video especially on python in Excel yes we can now write and execute python code natively in Excel and no more add-ins to install on Windows for writing the python code the new py function converts the formula bar into python mode you can easily import python libraries like a pandas c bond numpy matplotlib and more after loading the data into Data frames in Excel we can perform data analysis and build spectacular charts in this video I am going to cover step by step how to use python in Excel for data analysis and how to build code for charts even if you don't know anything about python we can use charge GPT to write the python code for us in this video I am using financial data for demonstration you can download the raw data file using the link provided in the description before we get into the topic one thing to remember is python currently available in Microsoft Office 365 and especially for beta channel to enable beta channel in Excel go to file click on account and click on Microsoft 365 Insider icon and click on change channel here choose your Microsoft 365 inside a channel you need to select a better Channel and click on agree to terms and conditions and click OK I have already enabled beta Channel I don't need to enable one more time so once you click OK it takes hardly two to three minutes to enable the python mode I'm going to cancel now adding a new sheet to keep all my data frames into one place I am going to rename data frames to change the Excel into python mode equals to py and hit tab the cell and the formula bar both are changing into the python mode now you can write the required python code to execute here instead of that the shortcut key is alt control shift and P the active cell changes to python mode now you can write any required python code importing the required libraries okay import found us as PD and when you hit enter it nothing happens you have to press Ctrl enter now this changes to python mode now you see here it came as none type because the change the library has imported but I have not given any label to this I am going to increase the formula bar to so that you can see the whole code and I'm going to give a label imported Ponders now press Ctrl enter now the label is given as imported on us here python and libraries now I am going to import the c bond Library equals to P by hit Tab and import C1 as SB I'm going to put the C1 Library as SB then hit enter going to give the label name as imported C1 press Ctrl enter now C bone Library also is imported now I have imported the both two required libraries for this financial analysis dashboard now let's load the data into the data frame I'm going to give data stream name as dear so equals to py d a equals to now I need to select the entire data go to the data sheet when the icon changes to our diagonal you can click on this now I have selected only the data range if I click one more time I can also select the data headers you can see the code it has included the headers as well now press Ctrl enter now the data frame is loaded you can see this left side little icon now it is in the data frame mode you can click on this now this will give you a preview of your entire data with top file rows on the bottom five rows you can scroll through it it will show all the data click on Anaconda icon it will take you to the respective website and you can learn many more things from here also going back to Excel and now we will describe the data frame equals to pydf dot describe this will give the basic statistics of the entire data frame and hit control enter now the DF dot describe command in the python object mode there is a little icon before the formula bar click on that you can see the similar icon matching to data frame click on Excel value to see the data it analyzes all the numerical columns within the data and shown with the basic statistics like a row count mean standard deviation minimum 25 percentage 50 percentage 75 percentage and maximum value from the each of the columns by default describe command will take all the numerical columns available within the data suppose if I want to focus only on specific fields to analyze units sold sale price sales and profit only then how to write the described command now let us ask charge GPT to write the code for us I have already opened the chart GPT going back to chart GPT and I have return the prompt I'm just pasting The Prompt hit enter now I can use this directly in my Excel sheet copy the code go to excel use ey then paste in the code and hit Ctrl enter now the data frame is in the python object changing to excel value we will get Only The Columns that we focused to analyze this is how you can leverage charge GPT while right in the python code now let us focus on to build the financial analysis dashboard I added a sheet file called financial analysis this charts Gallery is from C Pawn official website you can pick any of this chart to analyze so let us pick up I'm going to take the disk plot right click and open a new website and let's see the code you can directly copy this code and use in Excel before copying the code just wanted to show you the data set behind this chart right click on the load data set and open new tab you can see the GitHub link here click on the and here you can see the data so this is the data that they have used click on this mp3.csv this will show you the preview of the data loaded in that example so these are the column and this is the data I will provide you this link in the description you can use this data set for your practice copy the code head back to excel equals to ey and going to the formula bar and paste the code here they have imported the c bond Library if it doesn't need for us because I have already imported instead of SMS I'm going to use SP as a c bond library and I don't need this uh data set to load I have already have a data frame in changing SNS to USB and instead of MPG I'm going to use DF in the x-axis country the column axis segment hue is here I have done all the required changes here just press Ctrl enter now it loaded as the image right click and go to python output as Excel value now you can see this tiny chart here you can't even see this you can select multiple cells and merge and you can see the chart here which instead of merging the cells and see the chart just press Ctrl Z when you select the cell you can see this icon just click on it it will show you the chart just resize the required size move it around wherever you need now you can see for each segment one chart got created here and you can see here level 2013 is in Blue Line and 2014 is in purple line also the country access in the x-axis and the proportion rate is in the y-axis now let's see in another chart here head back to C1 chart Library I'm going to take this Rel plot open a new tab copy the code head back to excel equals to py and paste the code I don't need C1 to import one more time my C1 Library name is SB I don't need this data set to use because I have already data set I don't need this statement Instagram SMS SB real plot just change all the parameters the required parameters x axis units sold y axis sale price Hue country I am done with the changes hit Ctrl enter when you press Ctrl enter this has given error why because it says name error name MPG is not a defined because my data frame is DF since I used the volt data frame it is giving error directly you can give TF or else he can go back to your data frames list and pick the data frame press Ctrl enter now I am getting the error for weight for the parameter size let me remove this size and hit Ctrl enter now the chart is loaded so right click python print out Excel value now this loaded as a tiny object click on that and click on this linked object icon it will show the entire chart and you can resize however you need let's add the third chart head back to C bone visual Gallery I am going to take the joint plot right click and open a new tab copy the code head back to excel in the formula bar equals to py hit tab paste the code remove the libraries here and I'm going to remove the x-axis and y axis going to add my 1X axis x equals to DF when units is sold close bracket and Y equals to DF you can double quotes sales close double quote close bracket give a one line help us to read well hit Ctrl enter closing this errors and right click python print out Excel value click on this linked object and now the chart is ready you can resize going to reduce the formula bar and go to and remove the grid lines select all the charts and apply borders go to picture format apply border take order weight in the picture effects you can take shadow this is how you can write the python code to analyze your data in Excel using Python and to build this spectacular and amazing charts using python code in the first chart it is split at each segment level and analyzes the data and the second chart we have analyzed the units sold on sales price on the distribution by country in the third chart sales and units sold how they have distributed in the next video we will see how to utilize the slicers to make it interactive under Dynamic dashboards in Excel using the python code

Channel: Chethana Pixel

Views: 1,562

Keywords: excel artificial intelligence, actual vs target chart in excel, actual vs planned chart in excel, actual vs forecasted chart in excel, python dashboard, chat gpt excel, python financial dashboard, supercharge excel using python, microsoft excel, data analysis in excel, learn data analysis on excel in 10 minutes, how to build stock dashboard in python, chandoo, leila gharani, mynda treacy, mr excel, Introducing Python in Excel, Financial Analysis Dashboard, Excel Python ChatGPT

Id: ByQTxh8M1fE

Length: 12min 19sec (739 seconds)

Published: Wed Sep 20 2023