You will install and use libraries to manipulate data and automate processes in Microsoft Excel and Google sheets. Suitable for people with basic Python or good programming skills.
2 starting dates
Want to find out more?
Spreadsheet Data Analysis and Automation with Python Course overview
Suitable for people familiar with basic Python, this intermediate course uses Python frameworks to manipulate Excel spreadsheet and Google Sheets data. We use various Python libraries for manipulating Excel formats, especially OpenPyXL.
We also use xlsxWriter and tools for binary and legacy formats such as pylxsb, xlsd, xlst and xlUtils. These are used to manipulate cell data, worksheets, files, charts, format data and produce summary reports in Excel format, sometimes without opening Excel.
We also automate Excel with xlWings. People with VBA experience will find many of the operations familiar.
Who is it for?
Those familiar with Python basics looking to manipulate and automate data on spreadsheets.
Find out more about our Programming courses
Timetable
Spreadsheet Data Analysis and Automation with Python course takes place one evening a week for 10 consecutive weeks.
City Short Courses follow the academic year, delivering courses over three terms. These include:
- Autumn - October
- Spring - January
- Summer - April
Benefits
- Delivered by an industry professional
- Taught in small groups
- Awarded a City, University of London certificate
What will I learn?
In summary:
- Installing Python libraries such as Pandas
- Use openpyxl to manipulate data in Excel format
- Use xlwings to automate Excel
- Use Python to manipulate data in Google sheets
More detail:
- Review of Python data structures; Python documentation
- Installing Python libraries such as pandas, numpy, openpyxl
- Numpy data structures and functions
- Pandas data frames; slicing; summary functions
- Interacting with files and operating system (os library)
- Importing and Exporting Excel files, CSV files; exporting to HTML
- Excel Object Model. replicating Excel and VBA functionality
- Pivot tables
- Charting; Matplotlib library
- Installing Anaconda, using virtual environments
- Automating Excel and Office with xlwings and win32.com
- Working with Google sheets
Assessment and certificates
You will be awarded an official City, University of London certificate if you attend over 70 per cent of the classes. The course is not formally accredited.
Assessment
Informal assessment will take place through group discussion, class room activities, and questions and answers sessions as guided by your tutor.
Eligibility
Comfortable with basic programming in Python and installing software. No special maths knowledge required.
English requirements
You will need a good level of spoken and written English to enrol on this course.
Recommended reading
- Python for Excel – a modern environment for Automation and Data Analysis, by Felix Zumstein, O’Reilly.