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

[@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!

[@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

[@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!

[@knockn9ck]

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

[@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

[@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!!!!

[@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.

[@faizanarain3080]

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

[@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 :)

[@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!

[@عليالعرداوي-ض5ك]

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!

[@ildefonsogiron4034]

I never thought David Bowie was into computers!

[@DumpsterJuice4U]

We could be heroes!!

[@Brutserum]

Lmfao

[@azizmiya]

Lol, this David Bowie is a real rock star

[@drn_lvs]

Just for onde day...

[@luismontero3416]

🤣

[@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

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.

[@TheSoorajthevally]

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

[@VijayTiwari22981]

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

[@joaquinsancaro6285]

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

[@МаксимКлементьев-з3ы]

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

[@thejohnhoang]

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

[@Darille2022]

You are such a blessing. Subscriber from the Philippines here

[@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.

[@panditadata2595]

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

[@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

[@MrStuartAllan]

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

[@christopherdismuke723]

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

[@edson_winner]

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

[@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().

[@mr_anonymous_oo7]

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

[@ApteryxRex]

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

[@rasoulkhoshravan5912]

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

[@jamesmcgee1912]

Clear and straight to the point. Good stuff.

[@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.

[@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.

[@davidmuller5423]

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

[@Warrior11538]

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

[@claytonmarquardt5522]

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

[@chillagatturavindranath8089]

Thank you for sharing your knowledge on Excels.

[@Young10489]

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

[@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.

[@kennytieshisshoes]

Wow this was really straightforward and easy to understand.

[@QueenaJami]

So glad I found your channel!Thanks Derrick!!

[@markslima1557]

another outstanding video on python/pandas/excel tech!

[@espy58]

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

[@pablogcortez]

This tutorial is great, thank you so much!

[@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.

[@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.

[@ashokvarshan500]

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

[@expat2010]

The pacing of your videos is perfect.

[@robertopulido9557]

you are the best, straight to the point

[@ryanrumsey2830]

Thanks Derrick for all your videos. I have a question for you. How would I use Selenium to bring

[@retrofutur1st]

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

[@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

[@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

[@LornisHervilla]

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

[@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!

[@priya-ok9ur]

This video is really helpful. Thank you.

[@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.

[@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.

[@josepaulofernandes4052]

you made very good use of 10 minutes

[@TaxMentors]

Very informative . God bless and keep posting

[@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.

[@ER_aka_RAM]

Excellent demonstration of functionality 🤙🏽

[@erickballesteros4531]

dude, I took like 3 classes that made me use pandas and numpy and I just learned you could read excel files using them today smh. I was using openpyxl

[@toddai2721]

You need a refund from those classes.

[@_ia_wotblitz1400]

How did python know the path of the excel file?

[@MrMenace3210]

Great vid. Is it possible to order production run time in ascending or descending order to see how the other variable interacts?

[@BernhardWeber-l5b]

Very nice Tutorial. Thank you!

[@John5ive]

awesome video man!!!. to use the script over and over, you could add a section that reads the directory for all files in that directory . right? can you post a video that shows that?

[@ShahadIsmail]

Interesting!! Thank u this was very helpful👍🏻

[@OHoraceS]

In this case neither Python or VBA is the solution. Power Query will do just fine.

[@etcetc3800]

whats that?

[@mindelos1706]

@@etcetc3800 It's an Excel functionality that allows you to get data, transform it the way you want, load it and automate the process. To use Power Query you must own an Excel version equal or greater than version 2016. If you dont have Excel you can try it in "Power Bi Desktop" which is free Check some introduction to Power Query here: support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

[@OHoraceS]

@@etcetc3800 Hey there. Power Query is ETL (Extract, Transform & Load) tool that is part of Excel. In this case you tell Power Query to get the data from where you have your target files and than you do a lot of fun stuff with your data :) It's easy to use. You can google "Power Query" to learn how to use it.

[@ondrachladek918]

Is it possible to use power query to read my emails and automaticlly put the data into a excel file?

[@OHoraceS]

@@ondrachladek918 Sure you can do that. It can do that using the exchange data source. You can read several mails data, even attachments content, into a single Excel file.

[@fernandomgarin]

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

[@RafaMB]

Hi Derrick, so many thanks for all your tutorial, are great for me as a newbie with Phyton. I just had problem following this example, it seems that pandas is not recognizing the length neither the names of the sheets of your files, I'm using Pandas 1.1.3 version. What should I check o change, thanks in advance.

[@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.

[@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😢

[@cyrilllandau9935]

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

[@ai.simplified..]

1st step to analytics,thx

[@evelic]

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

[@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?

[@maxbart1353]

thanks, i learned so much from your videos

[@kinglukluk3016]

This tutorial is very useful... 👍👍👍

[@ExplodingGrandma]

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

[@shaziashabbir5813]

Thank you for sharing your knowledge

[@inigosanchez-ostizchivite5441]

Amazing tutorial. question: Why python paste the new files on your desktop, and why he knows, when reading the data that your excels are stored in your desktop.

[@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".

[@JHatLpool]

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

[@scotts1409]

Amazing. You made it so easy.

[@jcsgs769]

Derrick can you please teach how to create student report cards by taking excel data with outputs displaying pie charts analysis and picture of the students along with their respective grades?

[@renacake9492]

would you please create a detailed video on Dash-plotly? Particularly how to build a dashboard?

[@franciscomiranda3239]

Grear man. Can you make a graphic voronoi hexagon report with x,y points??

[@bicavalgyalvesdesousa2526]

Derrick you are super cool thanks for sharing your knowledge

[@ASSAGO1PTL]

Thanks for your videos Derrick - Had a question - Can you please show us how to compare two huge data frames ( 2 million rows, 150 columns) using pandas ? I would only like to see the differences in a csv format Thanks

[@muscababdinaasir2771]

Can you help me I wnna this code 😢

[@habeshadigitalnomad137]

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

[@TheYasinsonu]

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

[@arunavkhare2733]

Very nice video. I really liked the IDE. Can you let me know which IDE is this ? 🤔

[@lucianotavares4953]

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

[@MohammedAfsalM]

Great video ! I have one doubt regarding the efficiency of python script over VBA program. Which one is faster when it comes to reading thousands of Excel files one after the other for analysis of a column or cell content in each one of the Excel files.

[@mecanicoing9]

Hello Derrick, very nice short video. Can you point me to a good tutorial on how to make a GUI? I'd like to make a GUI for Excel Reporting Automation using Python.

[@durgaprasadchalamalasetty3642]

Nice presentation , really helpful my job

[@fredericnicholson80]

very good info, thanks for sharing. in a business environment I see that many times there are some "non-data" lines on top of the spreadsheet. Is there are way to use read_excel, but skip the first (n) lines (and also skip the the last m lines) ?

[@CodingIsFun]

You could use pd.read_excel(filename, header=0, skipfooter=0) skipfooter: Rows at the end to skip (0-indexed). header: Row (0-indexed) to use for the column labels. If your data starts from Excel Row number 4, you could type 'header=3' Hope that helps 😃

[@Rondondon747]

Your channel is so underrated.