How to Automate Excel with R
Written by Matt Dancho on October 7, 2020
This article is part of a R-Tips Weekly, a weekly video tutorial that shows you step-by-step how to do common R coding tasks.
Let’s learn how to automate Excel with R, using
Automate Excel with R Tutorial
Using R to automate Excel is an awesome skill for automating your work (and life). Your company lives off Excel files. Why not automate them & save some time?
Here’s an Excel File you’re going to make in this tutorial from R. 👇
First, collect your data using
tidyquant, an awesome package for importing & working with Financial Data.
The previous code makes this Pivot Table (stock returns by year and symbol).
…And this stock chart over time.
Now for the magic - Let’s add the Pivot Table & Stock Plot to Excel! 🔥
- Initialize a workbook
- Create a worksheet
- Add the stock plot
- Add the Pivot Table
- Save the workbook
- Open the workbook (programmatically)
You’ve just automated creation of an Excel Workbook with R.
It’s magic! 💥 💥 💥
SETUP R-TIPS WEEKLY PROJECT
Sign Up to Get the R-Tips Weekly (You’ll get email notifications of NEW R-Tips as they are released): https://mailchi.mp/business-science/r-tips-newsletter
Set Up the GitHub Repo: https://github.com/business-science/free_r_tips
Check out the setup video (https://youtu.be/F7aYV0RPyD0). Or, Hit Pull in the Git Menu to get the R-Tips Code
Once you take these actions, you’ll be set up to receive R-Tips with Code every week. =)
👇 Top R-Tips Tutorials you might like: