Automate Multiple Sheet Excel Reporting - Python Automation Tutorial | Full Code Walk Through (2019)

[@CodeWithDerrick]

Sorry for the technical errors on this one (learning premiere and animations to improve the videos haha) - Medium & Advanced level techniques coming soon!

[@soy34mb]

Hi, great video. Is it possible the output to MS Access file instead of Excel File? The reason is because I would like to combine thousands of excel files and the quantity of rows of the output will be more than 60mm.

[@ashleyramirez87]

Derrick Sherrill new stuff I just learned!

[@user-fp1pf7dr5j]

Where is live

[@luisfernandocuestasanchez4343]

Don't even worry about that man. You are doing God's work, thanks a lot. For all of us who have administrative jobs, this kind of content is amazing and I think college, at least in my country doesn't give us that (They didn't even taught us Excel). Now that I got out of it I will try to learn by myself and also with the valuable help of people like you. I have recently found in KZbin more than all the courses of my university. Thanks for the inspiration and being a role model too.

[@adamdenver8019]

i guess im asking randomly but does anybody know of a tool to log back into an instagram account?? I stupidly forgot my account password. I appreciate any assistance you can give me!

[@rccc4111]

This lesson would take more than 30 minutes for others to explain. Great pace & clarity. This is usually the best way to explain something for non-beginners. This is the essence! Thank you so much!

[@shaurya2544]

Really sweet person knows what he is doing and is not all about money and friggin sponsors. Keep it up! Makes me cry happily!

[@michelleamidon2083]

I'm going to drop all professionalism and just say " holy shit "". I've watched ALOT of training videos On python but WOW...it was short, clear, concise, and understandable. I know the video is 2 yrs old but I hope he is having all the success he deserves!! Well done!!!!

[@LarsHHoog]

Feels like more of a demo than solving a real world need. Accomplishing the same using power query in Excel is faster :)

[@goingfurther8092]

It’s crazy how well you describe everything. Your clear and slow speaking is great for tutorials. Thanks a ton!

[@omkarpatil2254]

Because he is robot

[@knockn9ck]

Holly.. you just convinced me of the worthiness of learning to program. I could automate 90% of my job!! Thank you

[@faizanarain3080]

Brilliantly explained....I have been trying to learn python since 6 mounths........only this Guy made python easy for my dumb mind....

[@asadmehmood9558]

I have seen power query more useful so far, I have been making interactive dashboards out of raw data from multiple excel sheets and when new data is updated in sheets just press refresh button and job done.

[@sreejithpnair8973]

COPY FOLDER NAMES TO EXCEL in ONE GO kzbin.info/www/bejne/oaK5m4aJn7uhodU

[@AXLAV]

"so far" ... thats' why

[@JohnsonKongor]

Thanks, Derrick. Great job. I have worked with excel for over 15 years, manipulating data with excel multiple functions. But I still give credit to python and other programming languages for data automation. I'm transitioning.

Hi Derrick, I was nine when Apollo 11 was launched so, despite the difference of timelines between you and me, I feel very enthusiastic about my Python learning curve. I will utilize your instructive videos in order to implement a project on a native american language I am learning here in Central America. As things go further in my idiom classes the more rules appear, therefore I am expecting about storing different sources in different Excel files and combine them, with the proper sintactic rules, in some sort of a centralized dictionary. I have recorded the important features of this video for further use. Thanks.

[@eddieflyer555]

Have to agree with the other Power Query comments - automating Excel reports using Power Query is super easy, built into Excel, and no coding required! People should look at the many KZbin tutorials to familiarise themselves with it. I'm not sure why you'd go to Python for the scenarios noted - Power Query is way more user friendly (built into Excel), less error prone, and low code to use Power Query. And apparently much quicker. I'm surprised from looking at the rest of people commenting and the number of likes this video received! Clearly people are not familiar with Power Query! Its like using a sledge hammer to crack a nut :)

[@amauta5]

Derrick, your video motivated to tackle a challenge at work. I already have a working solution using VBA, but it's not sexy. I want to use python. Do you think this is doable? I am putting the pieces together... Here's my scenario: I have hundreds of spreadsheets that contain delivery times for drivers. It kind of lists how long a driver took to deliver an order. When all rows are put together, i am easily looking at over 100K rows, each row is a delivery record. I want python to go to a folder where we keep all these spreadsheets, load them, put them together and run a pivot table and display it. I would want to see how many delivers were done by each driver every day. Drivers can log multiple deliveries at the same time. Due to insurance, i have to call out when the total delivery time exceeds certain limits. This python thing is sick.

[@satriobudi6090]

I'm from Indonesia and I support this channel

[@user-qd1bw8us1q]

Я новичок, но все понял. Очень классно объясняешь :) I’m just a beginner, but understood everything. You do it great :) Привет из России!

[@christopherdismuke723]

Great content. I don’t see enough people talking about excel -> python.

[@slyfox909]

This guy is going places! It wasn’t too long ago when I watched one of your videos and you only had a couple thousand subs. Keep up the great work! Love your videos

[@ildefonsogiron4034]

I never thought David Bowie was into computers!

[@NathanColmenero]

We could be heroes!!

[@Brutserum]

Lmfao

[@azizmiya]

Lol, this David Bowie is a real rock star

[@drn_lvs]

Just for onde day...

[@luismontero3416]

🤣

[@riteshtripathi8626]

KZbin's algo popped up your video and this the first video to watch on your channel and I am subscribed to it. Thanks, this has been a great relieve for my production planning, though I am new in python.

[@panditadata2595]

Love😍your videos... So clear and straight to the point...high quality

[@MrStuartAllan]

Brilliant video, I have heard python mentioned numerous times but never seen it in action. Looks amazing, thanks for making the video!

[@Darille1988]

You are such a blessing. Subscriber from the Philippines here

[@joaquinsancaro6285]

You have no idea how valuable this is to me. Thank you so much. Great content!

[@thejohnhoang]

dude thank you so much for this tutorial. i can't thank you how helpful you are to the data analysis community!

[@aduck24]

I really like your videos, like I’m purely a beginner but I feel like I learn a lot from your videos (and I already wrote some codes to automate excel tasks). Can you do more how python can be used to automate excel. Thank you and keep up your good work.

[@blackchristiangeek]

Thanks for the tips and I humbled to say that I never heard of some of these tools before this video. Accordingly, I have subscribed. Again, thanks, and God bless

[@ApteryxRex]

Just stumbled across this. Thanks so much - a clear, easy to follow example. Great stuff!

[@edsonwinnerify]

I have a lot of things to learn from you man. Keep your videos coming up! Thanks

[@PeladomanMaypa]

Subscribed i dont know anithing of panda, python, automatization, excel but your explaination ets so clear that i want to learn whatever u are saying.

[@TheSoorajthevally]

Very good one. I liked that you cared to mention which Python Version you we using.

[@quantum7401]

I like how you returned the number of populated rows and columns (4:42). I am new to Python, and I have been researching a good approach to processing inventory spreadsheets. What I am trying to achieve is a simplified, well sorted, printable table of what I will need to buy next month. So far I like "pyautoGUI, " but one of the biggest problems I am having is finding the number of cells that contain values from those that are empty, so I can tell Python where to start and end my code.-- And this is important because, I get brand new non-standardized spreadsheets that change in the number of available products way to often and I want to write a dynamic code. -- From there I plan to use autoGUI to hotkey Excel functions like: [=index, A1

[@CodeWithDerrick]

Hey Q uantum! I feel your pain. I worked in supply chain for a bit and I automated a system sort of similar to what you're talking about although not completely (here's a video if you want to check it out: kzbin.info/www/bejne/rmGUf2Shfrabi9U) Hard to say too much without seeing the spreadsheets, but pandas can distinguish empty cells - it just returns a NaN for those values and then gives methods to deal with them like .isna() or .isnull() so you could drop them, do some operation, or fill them with default values. I'm not too sure on PyautoGUI because I've never worked with it, but if you're just needing starting and ending rows to do an operation, you could get a series of all cells with values using .isna() (returned like [False, True, True, etc.]) and apply that to the length of the series/excel column. Always looking for projects like this for the channel so if you want to shoot me some sample data on linkedin or Twitter or something I'm happy to take a look!

[@quantum7401]

@@CodeWithDerrick, Thanks for the advice. I have been looking into Pandas. I think it will help a bunch with the .isnull().

[@claytonmarquardt5522]

really good stuff. videos like these are great because we're doing real work and it helps me learn! thanks again.

[@pablogcortez]

This tutorial is great, thank you so much!

[@expat2010]

The pacing of your videos is perfect.

[@VijayTiwari22981]

Very short and crisp video...to the point....Thanks

[@jamesmcgee1912]

Clear and straight to the point. Good stuff.

[@QueenaJami]

So glad I found your channel!Thanks Derrick!!

[@kennytieshisshoes]

Wow this was really straightforward and easy to understand.

[@markslima1557]

another outstanding video on python/pandas/excel tech!

[@Dividendology]

Really great tutorial!

[@chillagatturavindranath8089]

Thank you for sharing your knowledge on Excels.

[@LornisHervilla]

Thank you Derrick, great explanation!! You won a subscriber and a thumbs up. God bless you

[@mr_anonymous_oo7]

You have done a great and simple video to understand pyexcel in combo thank you bro😀😀😀

[@yiubili1]

I am very glad that you are more used to be the tutor in youtube. Thanks for providing the material with link! Thanks for your video, really liked them all.

[@ER_aka_RAM]

Excellent demonstration of functionality 🤙🏽

[@thomashayes1202]

BTW everything you refer to as a function is a actually a method. A function is a standalone set of instructions. A method is an instantiation of a function within a particular object.

[@Morphineck]

Great stuff, maybe pivot table could work for 1 or 2 different data sources, but if you got multiple spread sheets this seems like the best way to go. Maybe not as friendly as powerpivot but a good tool never the less.

[@muscababdinaasir2771]

Can you help me I wnna this code how I can get😢

[@georgetosounidis5545]

Hi Derrick, that was awesome! I'll make sure i'll check your content on Udemy as well :)

[@ashokvarshan500]

really very clear in conveying the things opened a new way to process my excel files thank u so much

[@shizzle5150]

I have tried on multiple occasions to utilize python where I would normally use VBA. Been playing with the xlwings add-on and think it's pretty cool. Unfortunately in most of my real world scenarios I end up reverting to VBA... Most recent task was to download multiple PDF files from links and then merge them into a single PDF document. My primary drive for this was to get away from Adobe Acrobat. Unfortunately every library I attempted choked on certain PDF files. I understand that this is because of the PDF files and not python but the end result is the same. I enjoy learning more about python but in practice most often VBA is the route I take because I have been using it forever. The graphing library does look attractive though and should I have the need to produce a large number of them I will look into it. Nice tutorial though.

[@Young10489]

wow thank you so much for the great tutorial. This is gold.

[@Warrior11538]

Derick you are doing a great job. God bless you.

[@dagudelo88]

great video, you should teach, how to make an automatic report that creates a complete report with the graph and the calculations on a PDF.

[@espy58]

I really enjoyed the video. Concise and informative. Thank you

[@Rondondon747]

Your channel is so underrated.

[@masbro-jk5bg]

Great content dude. I am newbie, and learn a lot from this video! Thanks!

[@cql8878]

but where is the "automation"? I thought I would see something like "when new data comes in, the exported excel file will also be updated automatically".....

[@quinnritiqal819]

Perhaps you didn't listen to the video at 8:58.

[@cql8878]

@@quinnritiqal819 But I still need to manually "run python" to append

[@quinnritiqal819]

@@cql8878 Correct! You probably also need to manually "turn on your computer", too.

[@cql8878]

@@quinnritiqal819 this reply shows how "smart" you are.

[@quinnritiqal819]

@@cql8878 wow, you really owned me

[@rasoulkhoshravan5912]

I need to practice in order to understand it. But thanks for the lessen. It is informative.

[@Sintarow1]

Overall this was a good tutorial, but I ran into a couple of issues. Now this might just be the IDE I was using (Pycharm) but I needed to install packages "xlrd" to recognize the excel file and "XlsxWriter" to be able to export the file as an excel. Posting this here in case others run into this issue with their code failing.

[@davidmuller5423]

Great video, thank you! But why not just use built-in excel fuatures power query and pivot tables?

[@lucianotavares4953]

Thank you for sharing your knowledge! Very useful easy to understand! 👍

[@JHatLpool]

To the author. This is a great demo of an important topic. Thumbs up +1 from me.

[@ExplodingGrandma]

not watching this but saw the thumbnail. your face frightens me.

[@TheYasinsonu]

Great Video! Helps me alot in day to day work. Thank you Derrick.

[@edwinpoh1559]

Nice video. Can you consider making a video on using python to automate and find out certain keywords of negative feedback inside multiple datasheets and create a new excel worksheet with the keywords inside?

[@robertopulido9557]

you are the best, straight to the point

[@markissleepy]

This is great for someone learning Python (like me), but as a real-world project, no sane person would ever do this when you could do the exact same thing with just Excel without any coding required, just using native functions.

[@CodeWithDerrick]

Sure, Pandas for sure can't replace excel. But it opens up some exciting doors. Best of luck learning python, Happy coding!

[@prophet78653]

The coding he is doing in Python is an one time investment...now think this task has to be repeated many times , then in each of the subsequent times, the developer will just have to run the Python code, and the result will be delivered in seconds or minutes...taking it to a level further , if this is deployed using Flask ,then the user will can access this service from a web-site.

[@merryjoy48]

I love your videos they make life lot easier for me. Keep up the good work. However, I would want to add that I believe jupyter notebook would be best for this use case. As they provide better illustrations and you can give hands on tutorials in them and also you can share them with us viewers too. They are very easy to make and are also very intuitive in their sage because of support fo markdown text and LateX. Cheers

[@CodeWithDerrick]

Thanks for the kind words! I'll do my best to keep the content coming. Jupyter for sure is a great tool & super handy! I'd probably use them in almost every video that I use excel data in, however my viewer retention typically drops 10-20% whenever I use them. Maybe it's just an additional hurdle to get over, or I need to explain it better. I'm testing it out in a course right now to see how I can better explain using Juypter notebooks. Once I get some more data from that you might see more here on the channel.

[@phil.pinsky]

Great video! I’m just getting into python to be able to pull data from webapi and convert json to csv. That would be a great video!

[@retrofutur1st]

thanks for a great vid! truly grateful being walked through this stuff

[@cyrilllandau9935]

You could use power pivot and power bi and it will be cleaner for a presentation.

[@gabrielrml]

Derrick - love your tutorials, could you let me know how you would STYLE the content of the output file as it is being written (I know its just text and ints but how would you for example format one of the output columns as currency - using xlsxwriter? I just cannot get it to work, and styling reports is very important. Forgive me if you already have covered this -perhaps you could point me in the right direction.

[@aspoonfulofknowledge]

How long have you been doing this? You are really good with Python. Do you code for a living and do you teach? Great job!!

[@evelic]

Excellent. It would be great if you could combine multiple files with multiple different columns and produce a single, clean dataset.

[@josepaulofernandes4052]

you made very good use of 10 minutes

[@juanalcaraz4300]

thanks for the upload this video. i wasn´t have idea how to use python for read excel. one greeting from argentina

[@chuckbecker4983]

I don't know exactly why it is, but this is the best tutorial I have ever seen. Have you done, or would you consider, a series of tutorials on PostgreSQL?

[@jamesdujardin6233]

Hey Derrick thanks for taking the time to teach us. I was wondering how to put the df_all, shift_productivity & the plot all in one excel file? Any help would be great! Thanks!

[@fernandomgarin]

Muchas Gracias Derrick, excelente tu video. Saludos desde Argentina.

[@priya-ok9ur]

This video is really helpful. Thank you.

[@habeshadigitalnomad137]

Great tutorial! am about to be a regular to your channel.

[@maheshreddy5751]

Could you please do a video on difference in complexity and number of lines of code between python and VBA? Basically a comparison video.

[@cu806]

Seems like Python takes to much

[@lancetschirhart7676]

Without commenting on lines of code (no idea) I think one draw of python is its flexibility and ubiquity. Can't train learning machines with VBA, right? So there's impetus to do a similar (even if it's slightly more) amount of work to get a grip on something that is ultimately more powerful in its infinite scope of applicability. But I really don't know much about VBA, could be underestimating it.

[@davidmiranda8979]

Hey Derrick, came across this video, pretty awesome, I want to learn more so I will keep watching. Question, what text editor you use in this video? Or what you recommend?

[@vamshikrishna-cm2vu]

Derrick used "Atom".

[@malcom91]

Amazing content here. Auto subscribed. Cheers from Spain :D

[@junaidmalik9593]

Hi Derrick, Desperately waiting for new videos from you. we haven't seen any in few days.

[@ShahadIsmail]

Interesting!! Thank u this was very helpful👍🏻

[@taherismail5425]

that was awesome video, i 'v never been seen one like that , thanks for your time my friend

[@BernhardWeber-l5b]

Very nice Tutorial. Thank you!

[@dylanyespersen]

Amazing Tutorial, Derrick. Thank you so much :)

[@yannickmorizot4502]

since the version 2.0 of pandas, used numeric_only=True on the mean() Otherwise you will faced an error TypeError: Could not convert to numeric pivot = df_all.groupby(["Shift"]).mean(numeric_only=True)

[@FutureappsTech]

Nicely demonstrated. Appreciate your efforts! But you dont need python to do all of these for excels. Excel offers graphs, pivots, formulas. And offcourse VBscript and power query. Can you try some scripting which can be embedded in excel that helps in VLOOK up. Thats real pain.

[@legochewbacca2214]

Glad to see Sid made a youtube channel. But real talk this is a very good video

[@kinglukluk3016]

This tutorial is very useful... 👍👍👍

[@DequanHarrison]

Awesome! Thanks! Would greatly appreciate if you could answer my question in your ""Replace Vlookup with python" video.

[@muthuraja3128]

Thanks for sharing python programing knowledge, i want to learn full course from you please share all videos links

[@CodeWithDerrick]

Hey Muthu Raja! So far I only have one course (that's pretty short but completely free) working on getting another one up in September about NumPy and Pandas! Here's my Udemy profile. www.udemy.com/user/derrick-sherrill-2/

[@rdmorillo0404]

Very Good Video Derrick!

[@maxbart1353]

thanks, i learned so much from your videos

[@MrGreyfoxut]

I have learnt quite a bit about python and still don't understand why you would use a text based editor rather than the formulas and tools within excel, having to do things blind just seems so silly to me, would love to know why you would do this especially when it gets more complex. :)

[@TaxMentors]

Very informative . God bless and keep posting

[@valensrwema]

Thank you so much for this amazing work . I have a question 🤔, if it is possible could you please make a video of how we can promote the categorical values ( like bad,good, very good, excellent)to become variables with yes or no values 😉 Thank you