Automate Pivot Tables and Charts with Python | Excel Automation Hacks

hey guys so this is a real-life example of a report that my team used to put together back in the day and we as a set about 18 to 20 of these reports every single day and they had to be manually done because they were created off of a pivot table which had a data set in the background and it took so much time what if I told you that I can take a report that'll take you 5 10 15 minutes to run on your own and automate it in less than a minute continue watching so in today's episode I want to do another version of some excel automation again this is actually a real-life scenario that happened to my team back a couple of years ago but when I was working for a large grocery retailer you know one of the things that we used to do is we used to do a lot of analysis at the department level and the problem is is that you know when I'm looking at data like this so I've simplified this data and obviously this is fictitious data but you know you can expect stuff like this in the real world and one of the things we used to do is by department we actually used to do an analysis the most basic analysis we used to do is we used to say all right by quarter and by year what are the average sales and what's your average margin rate for a specific department in this case it's sports bakery pet produce and meat and we would have what we call category leads or category VPS and what we would do is we would send them a summary sometimes on a daily basis sometimes on a weekly basis so I had a team of analysts I would get together and you know they would start looking at this by department and the problem is a VP only wants to see their department they don't want to see everything else so we used to get a massive file and you know we used to have to split out some of this data and if you want to know how to do that look at my previous tutorial which I will link above right now or you'll see it in the description and it's a great way to actually start parsing out your data into different components or into different departments well now that I have that the other thing they wanted to understand was they wanted to see a pivot so they want to see something like this where I had year down here I had q 1 q 2 you know Q 3 and Q 4 and then the average sales average sales here and something like average margin here and what my team would do is they would go in and they would literally create a pivot and so you know if you wanted to do this we would have to add in a pivot table so for example I would go to insert I would go to pivot table I would select the range like so and then I would say okay and then start creating my pivot now this is a good and all for one or two different areas but imagine having to do this every single day for every single department and then emailing these off to these executives and so you know I've created a sort of a makeshift little pivot table here and then you can also create a pivot chart out of this so they would want to see something like let me see if I can actually create it something like this where I have instead of some it'll be average so you have you know 2017 so this isn't ordered but you know you can order this eventually I don't know how this creeped over here but you know this is the funny stuff sometimes Excel does but you know they would want to know you know what what this trend looks like and margin here is not visible because it's the scale is so small so I would have to go ahead and create a secondary y-axis and so we'd be doing this every single time and then sending this out to them with a little note in there explaining you know what's happening now what if I told you that this process you know if you were to go through every single department and do this every single day and to build these charts and to build this stuff you know took upwards I mean there was a bit of automation put in place but took about 30 to 40 minutes if not maybe an hour actually of a specific analyst time who would just do this for about I believe it was 17 or 18 different main departments we did it for and then some of those larger departments have very large sub departments like for example if you look at grocery groceries built into various different types of sub departments and when you look at that when you look at something like that it becomes you know a lot more complicated because you're doing a lot more of them so I think he used to spend upward probably about an hour to maybe maybe an hour to an hour and a half or so a day putting this together because there was no automated way to do the way that we extracted the data was through SQL but there wasn't really a great system back then I'm not sure now and I think part of the reasons why companies least back then maybe even some now are a little bit afraid to start using a lot of robust systems for this is specifically for privacy I mean a lot of this information I mean this is very top level information but when you get down to specifics behind this you know when you're looking at advertising funds and when you're looking at you know vendor promotions and you know vendor funds given to you there's a lot of secrecy in some of those numbers because they're very proprietary to that specific retailer and so privacy was a big constraint which is why we used to do a lot of this in-house and we started building tools like what I'm about to show you right now you know and just on the note of privacy I would say privacy is one of those things that's becoming a hot topic nowadays you know just with all the incidents that have happened with big social networks and whatnot and it's always important for you to protect yourself and you know and so when I'm doing a lot of my own analytics and I want to protect myself against you know phishing incidents where I want to protect myself from or you know just keep my information private I actually use a VPN and I'll use proxies I'll use VPNs in combination and the VPN I use is actually called Nord VPN I'll put a link in the description for it I believe right now that are actually having a promo and I got to tell you guys I've been using these guys for about four years now and I've never had an issue so if you're interested check it out I'll put the link of the description anyways so now let me go ahead and walk you through how I've added all of this in under a minute so what I'm gonna first do is I'm gonna give you the demo so let's go ahead and open this up so let me get rid of this stuff because I don't want it to impact my demo so let's go ahead and delete all this stuff so what we've got is the bare-bones you know the data that was received is sales bakery pet produce and meat and I can make this all a little bit bigger for us but the numbers are different for all of them again fictitious numbers and so I want to create something similar to that pivot and that chart that I showed you that I can just ship off to whomever I need to so let's go ahead and look at this as a demo all right so what you're gonna see in this folder is you're gonna see two files you're gonna see one that's called sample data xlsx which is this file and then the other one you're gonna see is the actual script that I made so let me give you the demo and then we'll walk through the script afterwards on how it works so what I'm gonna do is I'm opening up a terminal count console here alright so let me just make sure I'm in the right directory here alright which I am so let me go ahead and close this because I want to make sure that just hit save I don't know what I did but some Souls get too safe okay so let's go ahead and run this so the first things first we're gonna right we're gonna run Python and so let me actually make this a little bit bigger for us all right so just I made that a little bit bigger but what you're gonna see here now is I'm gonna run this command so python and it's called pivot charts dot pi alright so when I run that it's gonna ask me for a file path very similar to the to the script that we created last week I drag and drop in here and I just get rid of that white space at the end and so now what it's gonna ask me is it's gonna ask me for the index and the index would be what are the x-axis that I basically want to put in here so I want to do - I want to look at here and we have to make sure that we write it exactly as it is in the excel file so a year and quarter and so what that's gonna do is it's gonna first give me all my years and then I'll give me more quarters next to it no commas no you know periods or nothing like that in between and so we're just gonna hit enter now what are the columns that I want so I'm interested in sales and I'm interested in margin so now as soon as I hit enter I'm not gonna hit it yet but let me tell you what it's gonna do it's going to create a file for each one of those different departments I should create five files in that it's gonna have the pivot and it's gonna have the chart ready for us so let's go ahead and run this and I was probably doing some debugging but I'll get rid of that and so here you see it's got produce sports pet meat and bakery let's go ahead and open bakery and when I open bakery what it's doing here is it's gonna give me the year then it's gonna give me the quarter the margin right in that quarter but I'll show you something else after annal you'll see that it's gonna do the average margin rate and then the sales value and here you see it actually plots a sack and y-axis on its own and let's guy your x-axis which is your year quarter cuz that's the order you put it in you said year first and quarter it's got your sales axis here and then it's got your margin axis here and so let's open another one just to make sure that it's done it right and so when I open this it says produce up there which is good it's got different numbers which is great and the chart looks different and let's open the third one up it's open up sport okay so sport looks a little bit different as well the other ones 41 this is 44 and the sales number is different so you can go ahead and tweak this way you want so let's run this a different way now so let's go back and let's delete these files I want to make sure that we're generating absolutely brand new files so now we're gonna do something a little bit different we're gonna write it the other way around we're gonna say this time so let's let's drag in the file path but let's say I wanted to know the average rate by quarter so this time I'm just gonna say quarter I don't care what year it is I just want to see if there's any relationships in the core and let's just look at sales alright so now I don't when I want to go ahead and open bakery it's gonna show me the average in that quarter and it's gonna show me what the sales amount was in that specific quarter on average for those last four or five whatever years it was which is great so now I can see this view as well I can do the same thing for margin if I wanted I can do the same thing by year by sales year by sales and margin so this just gives you an idea of some of the flexibility you can have and then I can write a really quick script like I mean within 5 or 10 lines of code tops that will automatically email this to the right owner as well and you can run this on a daily basis put this in a job run it let it go and it will work there's no question about it alright so now let's go through the code and see what that looks like so what I've done here is am using pandas obviously as did my data frame I'm using the matplotlib I'll explain what bytes died bytes IO is Anna and the OS path that we used last week and so what I'm asking you to do is go ahead and take in the file path take the index and then take the data and so this is where it was printing software it should know so let's go ahead and delete that we don't need it then I've said all right show me what extension this file is which is going to be XLS s in this case and then the file name and then the path okay so we're just we're just doing some basic path syntax here and so well now what I'm doing here is I'm saying set Excel equal to PT done excel file and then in brackets file and what I want to do is I want to create a list of my sheet names so if I've got one sheet it's gonna return a list of one sheet names if I've got ten sheets it's gonna return a list of ten sheet names and then I'm creating a list object in which I'm gonna go ahead and iterate through all the different sheet names and store it and then I can go ahead and call that by using an index so I can say file 0 is equal to sheet 1 which may be bakery file 1 is gonna be equal to sport file 2 is gonna be equal to Pat and so forth and so once you have this list object with all the different sheet names in there I want to go ahead and iterate through all of these different sheet names and I want to create a data frame for every single one of them so again file 0 maybe sport and so it's gonna create a data frame for sport for me file one could be pet and it'll do the same thing and it'll keep doing that and then along the way I'm saying going and create a pivot table out of that specific data frame so if it's file 0 we're gonna be going through this loop only on file 0 first when it's done this entire loop it'll go back and then do file one and so forth and so this command here is what creates a pivot table and what I'm suggesting is we do it based on the index and this is pave dot index which is going to be the user's input based on whatever they put in and then values is gonna be a bit of data based on whatever they put in all right so next is what I've done is I've said let's call the actual bar an image so what's what's happening in that excel file is it's actually taking an image taking a picture of it and storing it into the excel file and in order for me to do that I needed to create a value color variable called image in which is going to hold the actual plot value so I've basically taken my pivot table added dot plot at the end of it and then pulled it what I wanted to do and in this case it's a bar plot the secondary axis is and if I've entered more than one item in there then it's gonna take the last item as my y-axis typically you shouldn't have more than two it's gonna clutter up your graph but even if I had three say I had sales for let's say I had regular sales promoted sales and then I had margin rate your regular sales and promoted sales would most likely be on the same scale so it's okay so that's how this works really with is I want to have a slight gap in between my bar graph so I don't want to talk be all cleared together then that's a size and then the name it's gonna be based on whatever sheet name it is so if this is Pat it'll be Pat and so forth now in order for me to save this as an image I need a converted into a bytes IO object and then that's gonna allow me to store it in some kind of a variable and so when I go down and actually write it down when I and this is basic syntax writer equals PDF Excel writer I've reserved these two columns so one is gonna be my path so that's basically the path that I got it from it's gonna put it back in the same directory where your original file was and then slash the sheet name so it's gonna go on my original directory and it's gonna be called you know this is a sheet name meet dot XLS Bakery dot XLS and and so forth that's why you have this would be where it's stored this is the name dot xlsx and that's basically how that works and then finally I'm gonna go ahead and insert the image in column or in cell f1 and I'm returning image data which is basically the graph in this case so if we go you'll see that all of these are actually stored starting in f1 and you can go ahead and change that to whatever you want but you see that it actually starts in f1 which is right over there and if I put d1 it would start there I only put it out because you know I may have a certain number of columns and I didn't want it to overlap it otherwise you'd have to manually move it and so that's really it guys that's how the code works great way to automate things great way to even give control back to the user so that if they wanted to pull some kind of a custom query they can do that you know generally speaking the way we did this is we built something like this and we put it behind a user interface and they were able to go in and tinker around with the data but you know the feedback that we got from them was just it was amazing because they weren't waiting on people to generate this data nor that I have an analyst or a couple of stressed out analysts to get this out on time to a bunch of senior level executives and then at the same time for people that didn't want to go ahead and pull this we had automated jobs that would actually go ahead and you know kick this off in the background and then automatically send the email out to them so this is another good example of some automation that you guys can put in place hopefully enjoy this video if you did hit that like button hit that subscribe button and I will talk to you later on thanks take care and bye [Music]

Channel: SATSifaction

Views: 34,665

Keywords: Automate Pivot Tables and Charts with Python, Excel Automation Hacks, automate excel using python, excel python automation, excel hacks 2019, python script automation, python tutorial excel, excel spreadsheet hacks, excel hacks for productivity, one minute automation, python programming, how to program with python, advanced excel tricks, pivot charts, pivot tables, pivot tables in excel, pivot charts in excel, how to create a pivot table, how to create a pivot chart

Id: FO1xbO70pnY

Length: 16min 18sec (978 seconds)

Published: Fri Jul 19 2019