Excel Automation With Python

[Music] what is going on guys welcome back in today's video we're going to learn how to automate working with excel files using python so let us get right into it all right so we're going to learn today how to work with excel files in python how to automate certain procedures and this can be quite useful because of course excel has a lot of interesting functions a lot of ways in which you can manipulate your data sort your data change your data but in the end excel is not a programming language it has some aspects that can feel a little bit like programming but in the end it's not a solid programming language so maybe you know how to do something which you want to do on your excel files in python and you don't know how to do it in excel and in today's video we're going to learn how we can actually work on excel sheets using python uh and for this we're going to start with the very basics of the module that we're going to use today we're going to see how we can open workbooks how we can load workbooks save workbooks uh access individual cells and so on how we can do stuff like that and then we're going to look at a practical example something that could happen or that you could want to do we're just going to look at some sample data and how we can do certain operations on that sample data using python so the first thing we're going to do is we're going to open up a command line cmd for example and we're going to say pip install open p y x l this is the library that we're going to use today p y x l open p y xl um and this is what we're going to need in order to manipulate excel files so first of all we're going to start with an existing excel file and for this we're going to create it so we're going to open up exo the actual software and we're going to create a very basic workbook so we're just going to have a blank workbook here we're going to have some values 10 20 30 um [Music] 40 come on 50. 60 70 80 and 90 and then somewhere here hello world for example and we're going to see that we're going to be able to access those values in python so we're going to save that in this case on the desktop as book 1 and it should be saved there you go so now we can go there and rename this to [Music] myfile.xlsx we can drag and drop that into pycharm up here and now we can start with the coding so we can say import open pyxl and then we're going to say workbook is going to be open pyxl dot load workbook and we're going to load my file dot xlsx like that and in order to get the active sheet because an excel file has multiple sheets in order to get the active sheets we're going to say sheet equals workbook dot active and we can change the sheet title for example by saying sheet title changed like that now if we just want to print some values without actually doing some changes even though this is already a change but if you just want to get some information from that excel file what you can do is you can just say print sheet and like in a dictionary you can just print uh the or you can just access by uh specifying the cell so in this case a1 is the first cell and if i run this we should be able to see the value 10. actually this is the cell in order to get the value you need to say dot value so now we should get the value 10. there you go and of course i can copy that and i can change this to a2 a3 b1 b2 b3 and so on and we would get 10 20 30 40 50 60. uh actually we get 80. oh yeah of course because this is not how it works because we can actually uh we're listing the columns so we would actually have to do a1 then b1 then c1 and then a2 and b2 and c2 and so on now this should produce the results that we expected uh but this is how you can just basically uh look up the values of a certain cell and in the same way you can also set those values so we can say sheet a1 i want this to be i don't know 60 for example or let's say 900. now of course if i just run this nothing is going to happen because it's not going to change anything if i actually want the changes to be saved i need to say workbook.save and i need to specify the same file name myfile.xlsx of course i can also save it in a different file but if i do it like that and if i run this and if i open it up then you're going to be able to see that first of all this title is changed and then this value here is 900 so we successfully manipulated the actual uh workbook and also the excel sheet now of course you can enter all sorts of things so for example instead of entering 900 i can also enter hello world i can also enter the true value so for example um i can say just true without quotation marks and it's going to be true in excel as well um or i can say even stuff like let's say i want to have uh i know f 8 should be [Music] the string equals b2 plus c3 and if i now run this you will be able to see that in the cell f8 i will have actually the value 140 because it's actually b2 plus c3 so this is a formula and it's executed here as well so this is a basic uh the basic way in which we can read and write into read from and write into uh those workbooks and one thing that i want to show you here as well is we can create new sheets so we can say for example workbook dot create sheet and we can call the second for example and if we now want to change the sheets uh even though uh even if we don't create them ourselves so let's say a work but a workbook has like five sheets if we want to navigate through those we can just say sheet equals and then workbook and we can treat the workbook as a dictionary where i can say second for example here and then i can say sheet a1 is what is up like that and if i now run this and i open the file you're going to see that we have a second sheet here and this cell says what is up so we can navigate like that one thing that you need to keep in mind is if you want to save if you want to change something in the workbook the workbook is not allowed to be open in excel already so now let's go ahead and create a new workbook instead of loading an existing one so we're going to delete all of that and say workbook equals open pyxl dot workbook so this is creating an excel file inside of python and then saving it without having having an existing excel file to manipulate and here we can just say again sheet equals workbook dot active there you go um and now we can do the same things right we can we can go ahead and say uh sheet 1 a1 equals 10 for example and we can go ahead in the end and say workbook.save and we can save this as newbook.xlsx there you go and if i do this you're going to see that down here we have new book and if i open this you will see that we have the value 10 in here as we wanted it so this works and if i run this again you're going to see it's just going to be overwritten so there's no no problem here now let's look at a couple of things that we can do here first of all an easy thing is we can insert rows and insert columns so let's say we have something like sheet a1 is 10 and sheets what is it b1 and c1 is something as well so basically the same thing that we have in the other file we're now going to copy that paste that and now this is going to be [Music] two and this is going to be three let's say we have that so if i run this you're going to see that we have some values here in the beginning up here in the left maybe i should zoom in as you can see we have those values now if i want to insert a column or a row in the beginning what i can do is i can say okay let's say sheet dot insert rows and i can say okay where should this row be inserted it should be inserted in the first position or in the first row and if i do that you're going to see that we now have a blank row even though we set everything to a1 b1 and c1 now we have a blank row here we can do the same thing with the columns so insert uh columns at one the default amount is going to be one you can change that and increase that to more so let's first see what happens there you go you can see we have a new column and we have a new row it's basically the same as clicking here and say insert and insert and so on that's basically what we're doing um and now what we can also do is we can change the amount i think this is the amount keyword here amount five for example would mean five rows at position one let's say three columns at column one if we run that you're going to see that we don't have just one euro and one new column but we have five new rows and three new columns so this is something very basic uh one thing that we can also do let's just get rid of the columns and rows we can also merge cells so we can say uh sheet dot merge cells from a1 to c3 for example we can merge this into one big cell and then of course if i want to change something if i want to change the content i would have to address a1 because a1 is the the beginning of the cell um i'm not even sure if maybe if i address b2 it's not going to work as well now we're going to test that in a second so let's open this up you can see this is now one big cell and if i say sheet a1 equals hello world and i run this and i open the file you will see that the content has changed now let's see what happens if we change this to [Music] uh let's say to c1 because it's part of the range i'm not sure if it's not enough to just pick any cell no it doesn't work because it's a merged cell and this is read-only so we have to specify a1 here [Music] now we can also move something so let's say we have we don't merge anything but we want to move all these values here uh to the right and down maybe because they're in the upper left corner what we can do is we can say sheet dot move range and we can move the range a1 to c3 we want to move them two or let's say six rows to the right and the columns want to move them four rows four columns no actually we want to move them six rows down and four columns to the right so this is how we do that and actually not row if i run this oh we actually run around that already so if i open this uh you're going to see that this is moved down here and i think if we want to move to the left we would just have to specify some negative stuff so let's say want to move uh c c1 to [Music] c3 and want to move that as zero columns or actually negative one columns in zero rows so this would this would move it to the left i think let's see if this is true and yeah as you can see those were moved to the left and overrode what was in the b column uh so this is something very basic uh and i think we we now know the basics if you want to look at in uh if you want to look into other functions or parameters you can just look at the documentation of this module so those are the basics and now we're going to look at something that is a little bit more practical all right so let us look into a practical example we're going to use the excel file that i have prepared here and inside of that excel file i have some data first of all some basic title and then we have name we have birthday we have h and we have net worth as you can see the h column is empty we have some birthdays and the goal is now to calculate the age uh based on those birthdays uh now i don't say that you cannot do this with axel you can definitely do that with excellent some way but i'm one of those people i know how to do it in python right away i would have to google how to do it in excel maybe it's easy maybe it is not but there are definitely a couple of processes that maybe can be done in excel maybe cannot be done in excel but can definitely be done in python easily this is just one example we're going to use the birthday and we're going to use today's date to dynamically calculate the age now of course one thing that you can do in excel is you can just uh enter a formula and it's going to be up to date every time if you do it in python you do it you have to rerun it every time but for now we're just going to use this as an example we're going to take the birthday and calculate the h and then we're going to do one thing with a net worth here so what we need to know here is that the c column is actually the column with the birthdays and the birthdays are from row five down to uh row nine and the h is in the d column so this is what we're going to need and first of all we're going to import open py xl again and we're also going to import date time as dt and we're going to open the workbook so we're going to say workbook equals open pyxl open not open load workbook example dot xlsx and then sheet equals workbook dot active and now we're just going to say four row in range five to ten uh ten because if you say range five to ten this basically means five six seven eight and nine the ten is not included the five is this is just how the how the range function works um and then we're just going to get the date so we're going to say for each row give me the date which is going to be sheet and an f string of c and the row number and we want to get the value now you might think that you're going to get a string but the good thing about this module is that you're actually going to already get a date so you don't even need to process a string and turn it into a date you're already going to get the date uh from the cell um in addition to that we're going to say dt.daytime.now this is going to be today and the age is going to be calculated as today year minus date year and we're going to subtract today uh we're going to subtract if today month and today date uh day sorry is less than date month and date day so this would subtract one additional year which takes into account that we actually have today we actually have the birthday today so this is just for the edge cases um but this is how we calculate the h and now we just say sheet d what is it d row is an f string is going to be the h and of course in the end don't forget to say workbook dot safe example xlsx and of course every time you do that make a backup because if you screw up your code you screw up your excel file there's uh not really a way to recover it so make sure i'm opening up the excel file without running the code that's not very intelligent let's run the code just make sure you make backups every time now let's open the excel file and you will be able to hopefully see that we have the ages here does it make sense yes it makes sense 1999 21 uh because the birthday is on the uh 23rd which is two days from now so uh if we change this to 21 and rerun the script this person a person should be 22 already let's open this up again and you can see the person is 22. um so one more thing that i would like to do here is if we look at the file we can see that we have a net worth in us dollars let's say i don't want to have it in us dollars i want to have it in euros based on the current uh on the current exchange rate if i want to do that i can do this in python easily i'm not saying that you cannot do it in excel maybe you can maybe you cannot uh but you can definitely do that in python easily and for this we're going to use the pandas data reader so we're going to say import pandas and of course for this we need to install the panel's data reader so cmd pip install pandas minus data reader and you can use whatever you want you can use web scraping you can use an api you can use uh the forex python module the problem with the forex python module as of right now is that it doesn't work because of some changes in the yahoo finance api which i think it is using so for me it produced some errors which is why i use the pandas data reader and what we're going to do is we're going to say import pandas underscore data reader this is important you install pandas dash data reader but you import pandas underscore data reader we're going to import it as wep and down here we're going to say uh first of all usd to euro is going to be web dot get data from fred and this is why it probably works because it's not the yahoo finance api and all major problems at the moment are coming from there so if we use fret we're going to get uh at the moment at least no problems uh and the string that we need for the exchange rate in particular for us dollars and euros is d e x u s eu now if you want to know your string for another currency you can just look up the fret uh you can look up the threat website and see what the tickers are for your specific thing we are interested in the last position so in the most recent value and we're going to have to one more time access the column so d e x u s u and this gives us the exchange rate and for this what we need to do here is we need to say sheet um f e row e because the net worth is in the e column is going to be what it is at the moment so e row what it is at the moment so the value of that divided by the exchange rate so in order to convert from dollars to euros you have to divide in order to get from euros to dollars you have to multiply so that is it and we can now change the number format of this of the cell as well so we can say sheet f e row is going to be or not going to be it's going to be dot number format and the format i'm not sure how the format works i think this is exo specific but the format that we use here in order to get an a euro formatting is hashtag comma 0.00 euro that is how we get that format and by doing that we should be able to convert into euros let's see i think it's taking a while because of the of the web request uh now it's done and let's see what we have in the excel file we have the values converted to euro now we can convert them back if the chair if the rate didn't change uh we should see the same values as before so we can just go and say times and we can change this to dollar and if i run this we should be able to see that we have the same values or at least something approximating the same values let's see what axl says there you go we have the same values as before in a different format though i think but that's fine this is how you manipulate how you automate your excel how you manipulate excel files and how you automate your axle procedures using python so that's it for today's video hope you enjoyed i 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 next video and bye [Music] you

Channel: NeuralNine

Views: 15,368

Keywords: python excel, python xslx, python automate excel, excel automation, automate excel, python automation, openpyxl, python excel tutorial, python excel automation

Id: InPA2CGuCMk

Length: 24min 32sec (1472 seconds)

Published: Tue Jul 27 2021