5 Minute Python Scripts - Automate Multiple Sheet Excel Reporting - Full Code Along Walkthrough



** Five Minute Python Tutorial **
Automate creating excel reports from multiple excels sheets using Python

Uses xlrd is read in excel sheets, get their data, and perform data analytics.

Kite helps fund the channel, thanks for checking them out and supporting me —
⭐ Kite is a free AI-powered coding assistant that will help you code faster and smarter. The Kite plugin integrates with all the top editors and IDEs to give you smart completions and documentation while you’re typing.

Ideas with this script:

Connect to Python Time and run at certain intervals throughout a day with changing data sets
Create custom reports that you can connect to any excel sheet using matplotlib
& Plenty More

Code for the for loop used in the video:

i =0
total =0

for row in range(first_sheet.nrows):
if str(first_sheet.cell(row,1).value) == “Key Lime”:
i = i +1
total = total + (first_sheet.cell(row,2).value)
else:
pass

for row in range(second_sheet.nrows):
if str(first_sheet.cell(row,1).value) == “Key Lime”:
i = i +1
total = total + (second_sheet.cell(row,2).value)
else:
pass

print(i)
print(total)

print(total/i)

Let me know in the comments below if you would like a certain Python tutorial. Thanks!!

source

42 thoughts on “5 Minute Python Scripts – Automate Multiple Sheet Excel Reporting – Full Code Along Walkthrough”
  1. Hey Derrick thanks for the video! What do you do if your data isn’t very clean? I have rows where the numbers are on multiple merged columns and generally the formatting isn’t clean. How do I get to that point where I’m working with a format that can be utilized? Thanks!

  2. I am really happy to announce to you that the very best team I never regret working with they are great @Darkbruno on telegram he's very trustworthy and reliable bet..

  3. Derrick how on every fresh mail received from different id with attachment on outlook get automatically saved to our personnel folder . Can you please make a video on it , I will try many vba codes but they aren't successful .

  4. Hi Derrick, I have a data in excel spreadsheet and I am currently using PowerQuery to filter out the desired data. But I want to automate this whole task using python. Can you suggest me how can I do it?

  5. Hello very useful tut , i just need your help in my work because in my work want some thing like this and I looking for mentor for this part pls some help

  6. Hi I a excel sheet which has multiple hyperlinks in a row and has consecutive data next beside to it so can you help me how to open that link and print the data and click on next and repeat the print data again

  7. This was a great tutorial! Also if anyone has to automate this process for several more products rather than just "Key Lime", I found it easier to use dictionaries. For example…
    Define product name as a dictionary
    product_name = {}
    then use product['product_name'] for the counter

  8. I love your videos – I've been spending ages trying to learn to do Python in order to do excel automation. If we're learning Pandas, is it better to skip these and to straight to the tutorials you did that use Pandas? Thank you so much for these tutorials, you've saved me hours and hours of trying to figure this out on my own.

  9. But you can do the same tasks right in EXCEL application. No need to type Python scripts. Excel has built in functionalities and its very rich. If you know VB scripts you can run macros as well. Good presentation though, its kind of link between Excel and Python. Good job!

  10. Derrick I appreciate this video!!!
    I have about 10 reports that i update every Friday. I want to automate them updating so the new raw data gets sent to me via email and I was hoping I could take the raw data sent to me and have it take place of the old data from the week prior. In other words deleting the old data and adding the new data to the pivot table.
    I see your videos and I have an idea but if you could help that would mean a lot!!!

  11. I'm a beginner coding..been doing it for about a week, and I only understand close to 35% of what you are saying…but it looks super cool…hopefully in a month, I could understand much more..😁👌 thanks for the video

Leave a Reply

Your email address will not be published.

Captcha loading...