Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Extending Excel with Python and R
  • Table Of Contents Toc
  • Feedback & Rating feedback
Extending Excel with Python and R

Extending Excel with Python and R

By : Steven Sanderson, Kun
5 (5)
Buy this Book
close
close
Extending Excel with Python and R

Extending Excel with Python and R

5 (5)
By: Steven Sanderson, Kun
Buy this Book

Overview of this book

– Extending Excel with Python and R is a game changer resource written by experts Steven Sanderson, the author of the healthyverse suite of R packages, and David Kun, co-founder of Functional Analytics. – This comprehensive guide transforms the way you work with spreadsheet-based data by integrating Python and R with Excel to automate tasks, execute statistical analysis, and create powerful visualizations. – Working through the chapters, you’ll find out how to perform exploratory data analysis, time series analysis, and even integrate APIs for maximum efficiency. – Both beginners and experts will get everything you need to unlock Excel's full potential and take your data analysis skills to the next level. – By the end of this book, you’ll be able to import data from Excel, manipulate it in R or Python, and perform the data analysis tasks in your preferred framework while pushing the results back to Excel for sharing with others as needed.
Table of Contents (20 chapters)
close
close
close
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Conventions used
Get in touch
Share Your Thoughts
Download a free PDF copy of this book
1
Part 1:The Basics – Reading and Writing Excel Files from R and Python
Part 1:The Basics – Reading and Writing Excel Files from R and Python
Free Chapter
2
Chapter 1: Reading Excel Spreadsheets
Chapter 1: Reading Excel Spreadsheets
Technical requirements
Working with R packages for Excel manipulation
Reading Excel files to R
Reading multiple sheets with readxl and a custom function
Python packages for Excel manipulation
Opening an Excel sheet from Python and reading the data
Reading in multiple sheets with Python (openpyxl and custom functions)
Summary
3
Chapter 2: Writing Excel Spreadsheets
Chapter 2: Writing Excel Spreadsheets
Technical requirements
Packages to write into Excel files
Creating and manipulating Excel sheets using Python
Keeping it simple – exporting data to Excel with pandas
Advanced mode – openpyxl for Excel manipulation
Choosing between openpyxl and pandas
Other alternatives
Summary
4
Chapter 3: Executing VBA Code from R and Python
Chapter 3: Executing VBA Code from R and Python
Technical requirements
Installing and explaining the RDCOMClient R library
Executing sample VBA with RDCOMClient
Integrating VBA with Python using pywin32
Automating Excel tasks
Summary
5
Chapter 4: Automating Further – Task Scheduling and Email
Chapter 4: Automating Further – Task Scheduling and Email
Technical requirements
Installing and understanding the tasksheduleR library
RDCOMClient for Outlook
Using the Microsoft365R and blastula packages
Scheduling Python scripts
Email notifications and automation with Python
Summary
6
Part 2: Making It Pretty – Formatting, Graphs, and More
Part 2: Making It Pretty – Formatting, Graphs, and More
7
Chapter 5: Formatting Your Excel Sheet
Chapter 5: Formatting Your Excel Sheet
Technical requirements
Installing and using styledTables in R
Advanced options for formatting with Python
Summary
8
Chapter 6: Inserting ggplot2/matplotlib Graphs
chevron up
Chapter 6: Inserting ggplot2/matplotlib Graphs
Technical requirements
Some basics
Enhancing your Excel reports with plotnine2, matplotlib, and plotly graphs
Enhancing Excel reports with visualizations
An introduction to data visualization libraries
Creating graphs with plotnine (Python’s ggplot2)
Other visualization libraries
Embedding visualizations into Excel
Summary
9
Chapter 7: Pivot Tables and Summary Tables
Chapter 7: Pivot Tables and Summary Tables
Technical requirements
Making a table with the Base R xtabs function
Making a table with the gt package
Creating pivot tables with tidyquant
Creating and managing pivot tables in Python with win32com and pypiwin32
Creating pivot tables with Python: the basics
Summary
10
Part 3: EDA, Statistical Analysis, and Time Series Analysis
Part 3: EDA, Statistical Analysis, and Time Series Analysis
11
Chapter 8: Exploratory Data Analysis with R and Python
Chapter 8: Exploratory Data Analysis with R and Python
Technical requirements
Understanding data with skimr
Using the GGally package in R
Using the DataExplorer package
Getting started with EDA for Python
Data cleaning in Python for Excel data
Performing EDA in Python
Summary
12
Chapter 9: Statistical Analysis: Linear and Logistic Regression
Chapter 9: Statistical Analysis: Linear and Logistic Regression
Technical requirements
Linear regression
Logistic regression
Performing linear regression in R
Performing logistic regression in R
Performing linear regression in Python using Excel data
Logistic regression in Python using Excel data
Summary
13
Chapter 10: Time Series Analysis: Statistics, Plots, and Forecasting
Chapter 10: Time Series Analysis: Statistics, Plots, and Forecasting
Technical requirements
Generating random time series objects in R
Time series plotting
Auto ARIMA modeling with healthyR.ts
Creating a Brownian motion with healthyR.ts
Time series analysis in Python – statistics, plots, and forecasting
Time series plotting – basic plots and ACF/PACF plots
Time series statistics and statistical forecasting
Understanding predictive modeling approaches
Time series forecasting with deep learning – LSTM
Summary
14
Part 4: The Other Way Around – Calling R and Python from Excel
Part 4: The Other Way Around – Calling R and Python from Excel
15
Chapter 11: Calling R/Python Locally from Excel Directly or via an API
Chapter 11: Calling R/Python Locally from Excel Directly or via an API
Technical requirements
Calling R and Python from Excel locally
Why you would want to call R/Python from Excel locally
Setting up an environment
Calling R/Python directly from Excel
Calling Python from Excel using xlwings
Calling R and Python from Excel via an API
An introduction to APIs
Open source solutions for exposing R as API endpoints
Open-source solutions for exposing Python as an API endpoint
Calling APIs from Excel VBA
Summary
16
Part 5: Data Analysis and Visualization with R and Python for Excel Data – A Case Study
Part 5: Data Analysis and Visualization with R and Python for Excel Data – A Case Study
17
Chapter 12: Data Analysis and Visualization with R and Python in Excel – A Case Study
Chapter 12: Data Analysis and Visualization with R and Python in Excel – A Case Study
Technical requirements
Getting visualizations with R
Performing a simple ML model with R
Getting visualizations with Python
Performing a simple ML model with Python
18
Index
Index
Why subscribe?
19
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
Download a free PDF copy of this book

Summary

Embedding visualizations in Excel reports offers a practical way to enhance data presentation, customization, and automation. Whether you’re working with sales data, financial reports, or any other dataset, this approach empowers you to create insightful reports that convey your data-driven insights effectively.

In this chapter, you learned about data visualization techniques in general, the ways to extend your Excel workflows and reports with those techniques, and how to implement them in R and Python.

We have covered the most popular and powerful R packages and Python libraries in detail. You learned about the typical plots used to carry out your analysis and how to communicate your findings, along with use cases and examples where those data visualizations can be used.

The next time you prepare an Excel report, you will be in a position to dazzle your audience with the most beautiful and insightful visualizations they have ever seen!

In the next chapter...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Chapter 8
Next Chapter
bookmark search playlist download
font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY