I am working as a Senior Financial Officer dealing with financial reporting. This video gives me a new powerful tool to quickly manipulate and process a large volume of data. I think I will surprize my boss with my new skills! Thank you, Tim!!!
I'm a tax accountant, this is exactly what I've been doing for the past couple of years. Bringing in so much value to the business, and plugging the cash leakages using coding skills, you get taken more seriously.
You can do the same thing if you use SQL except the SQL C.L.I. is going to be easier to bring up a sheet and view it entirely , You just use the SHOW command perhaps also look into that i reckon there's a time and place for SQL CLI and a time and place for Python with Excel Python as a programming tool is unbelievable , to do financial analytics , Sure. But someone has to write the script to achieve your goal Excel is also good for that as well as importing data from external websites and databases but python is certainly more powerful than excel so.. Time and place for everything
New to python and starting my own project working with excel files. Tried to go through the documentation for openpyxl and was wasting alot of time learning unnecessary information based on what I was trying to accomplish. You've done a wonderful job with this and I was able to easily follow along and experiment along the way. Put together a py file for my notes on this module. Tim is Great!
exactly what i needed, im a civil engineer and learning python. i was coding a building cost estimator desktop app in tkinter and needed python after calculating all materials for me, it should also create an excel workbook and type those results for me. so that i simply print out for the client in the shortest time possible. awesome video. thank you sir. im a complete beginner though, any one with a better way of doing it is welcome
Hi, I am an industrial engineer and currenly looking for ways to automate the process of some report that we do in excel. tkinter might be a good idea for people that don't have the expertise or the knowledge of python cause they are just going to use the interface. hope you get what you want. Really appreciate if you reply the result if you finish the project. You should try KNIME too. Good luck. Cheers!
Actually did something similar a month ago, where I converted the excel file in to base64 in the end and sent it through an API to then generate it back into xlsc format through typescript. PyXl is amazing btw, anyone who watches this should try it. Very easy to use.
to those who are thinking @6:52 that you can directly open the workbook or directly save the workbook using that load_workbook command.. no ppl.. you need to first make or upload a workbook at the location of your text editor or some random location then take that location from there .
For anyone like me who finds the lettering system tedious and annoying, rather than using the get column letter function you can just use sheet.cell(1,1) for example which would be cell A1, but I think this is nicer especially if youre using nested for loops.
Thank you! So, to stick more to the tutorial, it will be like *ws.cell(1, 1).value = "Type something"*. By the way, you have a similar reference system in VBA as well.
As Tim noted, you cannot save the spreadsheet changes if it is opened in Excel (or in my case, Libre Office) for 'normal' use. I did figure a sort of workaround to this problem. If you open the spreadsheet as read only, your python code will run without a problem. Now to view the changes simply reload the spreadsheet. The reload command in Libre Office can be found under File >>> Reload (or press the keys Alt, f, l in that order(Note l is lowercase L)) Yes, you have to remember to reload the spreadsheet every time you make a change, but it is quicker than the close, reopen way, you could put a reminder in your python code. Note, if you want to edit the read only open file, there is a button on the top right that says Edit Document. but remember after editing it needs to be saved and reopened as read only. Use the python command try when doing a workbook save, and if it throws and exception, then ask the user to go through the close/reopen read only routine.
I started the transition from VBA to python+OpenPyXL around 2 weeks ago (after years of experience with VBA) and there it is - Tim's video about this very topic!
@@Bayo106and less intelligible than Visual Basic, I accomplishes so much via VBA in all Microsoft applications, disliked python syntax from day one but being coerced to learn it due to job requirements. I am still on the learning path and still like visual basic syntax more.
For anyone reading there are random real-world use-cases for stuff like this on occasion, I recently did something similar with Word and Powershell. In my case, I would take data from a CSV and generate a document containing stuff like login information, Employee IDs, etc. It can save time when you are doing 25+ of these documents.
Pretty much for the reasons Dave described. When in Rome, do as the Romans do. For general purpose stuff like this in Windows environments, PowerShell is the better tool for the job, not because it's better than Python or anything silly like that but because it's the lingua franca of Windows and has plenty of cmdlets to deal with the tasks you'll run into in a Windows environment but just as importantly the people I work with have a better chance of understanding my code if it's in PowerShell vs Python. For a more specialized task maybe I dip into the .Net APIs using PowerShell (use the native PowerShell solution first as best practice) or pull out C# in a Windows Environment. To choose an example where I would definitely prefer Python is anything in the Data Analytics or Machine learning world. On the operations side if you ever need to deal with networking equipment, the community has built some really good libraries for automating working with routers and switches. Network Automation and SDNs are a thriving space in Python. I actually tooled around with some Unifi stuff in Python while trying to automate performing resets of multiple Unifi Access Points. (Didn't really work out but learned a bit)
I love Python, but Excel already has VBScript built in. In my work, I haven't had many cases where the python add in was more useful than writing my own macros in VBScript. It's not all that hard a language to learn anyway.
Thank you Tim for the video. Its helpful for people like are in the process of picking up the rudiments of python. Could you do a video about OpenPyXL + Pandas. How these two work together and what are the pros and cons of using openpyxl and pandas on their own
Tim. Could you do an equivalent video Automating LibreOffice Calc ?? It would be fantastic. I'd even give a reasonable financially contribution for the effort.
I expected this to be as easy as working with the csv module, but obviously it isnt, and now that i think about it, why would it be. Nevertheless, this video help me understand it even more than i understand the csv module, from installing OpenPyXL, to formulas and cell styling. Great video Tim! Thanks for all that you do for us python noobies☺
I loved the video and would love a tutorial doing the reverse of the last project showing how to take a workbook and use that information to create a dictionary out of it
Great Tutorial Tim :) one suggestion: To open the excel file you created or working on, instead of going to the designated folder and find the file, you can just open it from the project tree.
A very good video. Thanks Tim. I think that you could have saved some time in this video, if you would have added a close, open excel file function within the Python code, and would have called it up each time you test your code. That way, it would close that particular file, save it and reopen it automatically without you needing to do it manually, as you did in this amazing video. Lemogan
Start a high paying tech career making $60k+/year with NO DEBT: coursecareers.com/a/techwithtim
And support. Thanks tim
What kind of keyboard are you using. I miss those clicks
I think some bug is there not accepting the mobile number or land line no. with & without the city code and country code. Or a fake one
🇬🇧🇽🇰🌟🌟
Did you love yourself amigo? :)
I am working as a Senior Financial Officer dealing with financial reporting. This video gives me a new powerful tool to quickly manipulate and process a large volume of data. I think I will surprize my boss with my new skills! Thank you, Tim!!!
I'm a tax accountant, this is exactly what I've been doing for the past couple of years. Bringing in so much value to the business, and plugging the cash leakages using coding skills, you get taken more seriously.
@@SwahiliSpicE the excel docs are created from your ide or within excel? seems VS code will not bring in excel file created in excel?
You can do the same thing if you use SQL except the SQL C.L.I. is going to be easier to bring up a sheet and view it entirely , You just use the SHOW command
perhaps also look into that
i reckon there's a time and place for SQL CLI
and a time and place for Python with Excel
Python as a programming tool is unbelievable , to do financial analytics , Sure.
But someone has to write the script to achieve your goal
Excel is also good for that as well as importing data from external websites and databases
but python is certainly more powerful than excel
so.. Time and place for everything
recently got into learning python.
my mind is blown with the versatility of python. I now wish i had done software engineering back in school..
New to python and starting my own project working with excel files. Tried to go through the documentation for openpyxl and was wasting alot of time learning unnecessary information based on what I was trying to accomplish. You've done a wonderful job with this and I was able to easily follow along and experiment along the way. Put together a py file for my notes on this module. Tim is Great!
exactly what i needed, im a civil engineer and learning python. i was coding a building cost estimator desktop app in tkinter and needed python after calculating all materials for me, it should also create an excel workbook and type those results for me. so that i simply print out for the client in the shortest time possible. awesome video. thank you sir.
im a complete beginner though, any one with a better way of doing it is welcome
Python is probably the best way to do that. I don't think you necessarily need tkinter though
Hi, I am an industrial engineer and currenly looking for ways to automate the process of some report that we do in excel. tkinter might be a good idea for people that don't have the expertise or the knowledge of python cause they are just going to use the interface. hope you get what you want. Really appreciate if you reply the result if you finish the project. You should try KNIME too. Good luck. Cheers!
Hands done one of the best Python training videos out there I’ve. Method of instruction, clarity and examples are brilliant. Thank you so much.
This is quality stuff. This guy gets how to make a good tutorial video. No wasted time, points are explained enough to understand then moves on.
Boomers at my work will now treat me as a god
I am at my final year, and just got to know about automation and how seniors don't know it. :v
I also wanna feel how you said 😂
🤣
No, they won't
for a quick minute until they start doing themselves to save their jobs
Actually it is exactly like that! :D
by far, the best video for starting excel with python
Actually did something similar a month ago, where I converted the excel file in to base64 in the end and sent it through an API to then generate it back into xlsc format through typescript.
PyXl is amazing btw, anyone who watches this should try it. Very easy to use.
Clear, rich, concise and well delivered. Thanks!. Clear, rich, concise and well delivered. Thanks!.
Was waiting for such a vid for soooo looooong. Thx Tim :)
Haha ikr. I learned a bit of Openpyxl for a script I run everyday, but this should let me do some more interesting stuff ;)
to those who are thinking @6:52 that you can directly open the workbook or directly save the workbook using that load_workbook command.. no ppl.. you need to first make or upload a workbook at the location of your text editor or some random location then take that location from there .
I've been learning Python by following a book and got confused and stuck for so long until I found this video. Thanks
You are a life saver dear, the way you teach is the best
Who the heck is this guy!! Just amazing trainings and super easy to follow along. Keep them coming Tim, much appreciated.
very well organized teaching methodology and great way of walking through.
this is a whole another lvl. of automation with python
AMAZING, Tim. The best demonstrated matter I have ever seen.
As someone who loves Excel and is learning and loving python, I think this module is awesome.
Than look at pandas
@@CrypticConsole I new pandas dealt with data. I didn't know it could access Excel docs. Thanks.
man, I really appreciate your work and time spent on explanation. I would never sort it out without your help
This strategy is bomb!!! I won 3/4 just testing it out!!! Thank you for sharing!!
Wow ! All the basic fundamentals are packed in one demo ! Thanks Tim !
For anyone like me who finds the lettering system tedious and annoying, rather than using the get column letter function you can just use sheet.cell(1,1) for example which would be cell A1, but I think this is nicer especially if youre using nested for loops.
Thank you! So, to stick more to the tutorial, it will be like *ws.cell(1, 1).value = "Type something"*.
By the way, you have a similar reference system in VBA as well.
thats the way to handle columns greater than "26" also.
Char(64+column number)
Clear, rich, concise and well delivered. Thanks!
As Tim noted, you cannot save the spreadsheet changes if it is opened in Excel (or in my case, Libre Office) for 'normal' use.
I did figure a sort of workaround to this problem.
If you open the spreadsheet as read only, your python code will run without a problem. Now to view the changes simply reload the spreadsheet.
The reload command in Libre Office can be found under File >>> Reload (or press the keys Alt, f, l in that order(Note l is lowercase L))
Yes, you have to remember to reload the spreadsheet every time you make a change, but it is quicker than the close, reopen way, you could put a reminder in your python code.
Note, if you want to edit the read only open file, there is a button on the top right that says Edit Document. but remember after editing it needs to be saved and reopened as read only.
Use the python command try when doing a workbook save, and if it throws and exception, then ask the user to go through the close/reopen read only routine.
Libreoffice Rocks
Awesome video Tim! Ive struggled a bit with python and definently had some 'aha!' oments watching this! Keep up the good work!
the posibilities and list of errors is so good as it helps
The best video about openpyxl. It is helpful for me a lot.
Bruh! You literally made me safe countless hours of work at my job! Lovin' it
Glad I could help!
Thanks for a great video. It helped me automate several redundant tasks and opened my mind to the power of openpyxl
I started the transition from VBA to python+OpenPyXL around 2 weeks ago (after years of experience with VBA) and there it is - Tim's video about this very topic!
Why did you decide to change? What are the benefits of using Python in this case?
@@micheleluigimelis426 way less code
@@Bayo106 not having to declare variables to begin.
@@Bayo106and less intelligible than Visual Basic, I accomplishes so much via VBA in all Microsoft applications, disliked python syntax from day one but being coerced to learn it due to job requirements. I am still on the learning path and still like visual basic syntax more.
Thank you so much Tim for sharing this. God bless you and all the best for you
Excellent run-through of this. Very cool to be able to interact with Excel spreadsheets in Python!
Thanks for this - Nicely done!
i liked ur comment cause it had no likes. It a lonely comment
pains of suffering of reading stackoverflow of excel automation and alien language has come to an end 😌
Thank you for this, it helped me a lot for an college assignment
< 3
For anyone reading there are random real-world use-cases for stuff like this on occasion, I recently did something similar with Word and Powershell. In my case, I would take data from a CSV and generate a document containing stuff like login information, Employee IDs, etc. It can save time when you are doing 25+ of these documents.
why powershell?
I use powershell alot, it does alot of the same things python does and it's integrated into the OS
Pretty much for the reasons Dave described. When in Rome, do as the Romans do.
For general purpose stuff like this in Windows environments, PowerShell is the better tool for the job, not because it's better than Python or anything silly like that but because it's the lingua franca of Windows and has plenty of cmdlets to deal with the tasks you'll run into in a Windows environment but just as importantly the people I work with have a better chance of understanding my code if it's in PowerShell vs Python.
For a more specialized task maybe I dip into the .Net APIs using PowerShell (use the native PowerShell solution first as best practice) or pull out C# in a Windows Environment.
To choose an example where I would definitely prefer Python is anything in the Data Analytics or Machine learning world.
On the operations side if you ever need to deal with networking equipment, the community has built some really good libraries for automating working with routers and switches. Network Automation and SDNs are a thriving space in Python.
I actually tooled around with some Unifi stuff in Python while trying to automate performing resets of multiple Unifi Access Points. (Didn't really work out but learned a bit)
The best tutorial video about python excel I have ever seen $.$
very clear thank you!
Congrats, great presentation. Got very interesting points. thanks
You are really talented in carrying information across. Thanks for this video.
Appreciate the comment :)
Thanks you so much for explaining in so detail and easiest way. Thanks a lot ❤
I love Python, but Excel already has VBScript built in. In my work, I haven't had many cases where the python add in was more useful than writing my own macros in VBScript. It's not all that hard a language to learn anyway.
I have a work that recently needed this--- JUST IN TIME! thank youu
Can u help me with the scenario u have in ur work. Thanks
Thank you for the install error explanations. Thats something most videos lack
Sehr gut! Danke! Viele Grüße aus Deutschland!
Crystal clear. Thank you.
It helped for creating ustom excel file from python! Thanks a lot
Thank you Tim for the video. Its helpful for people like are in the process of picking up the rudiments of python. Could you do a video about OpenPyXL + Pandas. How these two work together and what are the pros and cons of using openpyxl and pandas on their own
This is a good example of why I love Python even more than VBA. It is so intuitive and readable compared to most other languages.
totally false statements. Nice try simp!
Awesome, was searching for this. Next up, extract data from table in a PDF file, please :)
Oooh yes!!
The python package "Camelot" can already do this.
dude. this is sooooooo helpful, thank you
Fastest way to get started. Let's go.
Thank you for this great intro!!
I'm From Brazil!!!!! Perfect Video!!!!
Awesome tutorial and my only frustration is time limitations to apply all that you have shared. Thanks, and God bless
Very good, now i'm not tired for some accounting work.
Tim. Could you do an equivalent video Automating LibreOffice Calc ?? It would be fantastic. I'd even give a reasonable financially contribution for the effort.
I expected this to be as easy as working with the csv module, but obviously it isnt, and now that i think about it, why would it be. Nevertheless, this video help me understand it even more than i understand the csv module, from installing OpenPyXL, to formulas and cell styling. Great video Tim! Thanks for all that you do for us python noobies☺
Thank you brother ❤❤❤
Cool video! I didn't think that it is so easy(thanks man who make this library). After this video, it's obligatory to subscribe.
Most educational thank you Tim !
Great video, it helped me a lot with a project that i have and i only knew how to work with CSV files that didnt helped me at all. Thank you
Great video! It helped me a lot starting in Python! Many thanks!
Great video. Super useful. Thanks, Tim!
I loved the video and would love a tutorial doing the reverse of the last project showing how to take a workbook and use that information to create a dictionary out of it
Thks for the intro. Great vid.
Great video! Well explained!
This video was a blessing to me, Thanks very much!
Great Tutorial Tim :)
one suggestion: To open the excel file you created or working on, instead of going to the designated folder and find the file, you can just open it from the project tree.
wow...this is so very helpful...Thank you
Great work! Thank you for this! Subscribed!
Thank you for your great explanations ❤️
Best tutorial about Excel automation. Thanks, Sir
That now kinda useless with power automate desktop built in windows 10/11
this was very helpful for my internship (at a messy start up)! Thank you so much Tim!!
Bruhhhh needed a video like this 4 months ago
Tim, thanks a lot for detailed video. Simply the best way to use Excel formulas with get_column_letter, can't figure it out myself
Using python for sheets is very helpful,I use it on a regular basis
Great work. Good to get started.
thanks i will come back for more i need to watch the full video. but yes i admit you helped me with what i wanted thanks brother
Nice video! Great for a beginner to get comfortable with Python and Excel
Your videos are straight 🔥🔥🔥🔥 as a programming newbie I'm learning alot from you. Thanks for all you do!
A very good video. Thanks Tim.
I think that you could have saved some time in this video, if you would have added a close, open excel file function within the Python code, and would have called it up each time you test your code. That way, it would close that particular file, save it and reopen it automatically without you needing to do it manually, as you did in this amazing video.
Lemogan
Opening a file is easy obv but dont know how you close a file cause once its open the application itself is usually responsible for this
Thx Tim!
As usual, very useful. Thank you!
the master has spoken
Thank u so much for the video!
This is great! Thanks for sharing!
19:00 why not just do
for row in range(1, 11):
for col in "ABCD":
print(ws[col + str(row)].value)
Thank you so much 👍👍👍👍🌟🌟🌟🌟
Thank you for your Great Session
Well done. Thanks. Bob
Thank for vid. Its help me alot
Man, you can do a lot of sheet with Python these days!
Wow. Thank you so so much for this.
Thanks, super video tutorial.
Amazing Work
Am your new subscriber 🤝✨♥️your content and presentation style is 😘👌
Thanks for your video, you helped me a lot in my work :)
tnks tim its very helpfull for me.
lov u man