How to Automate Excel with R

Written by Matt Dancho

---

Your company lives off them… Excel files. Why not automate them & save some time? Here’s an Excel File you’re going to make in this tutorial from R. Let me show you how to automate Excel with R in under 5-minutes, using openxlsx and tidyquant.

Automating Excel from R

R-Tips Weekly

This article is part of R-Tips Weekly, a weekly video tutorial that shows you step-by-step how to do common R coding tasks. Pretty cool, right?

Here are the links to get set up. 👇

• Get the Code
• YouTube Tutorial

Video Tutorial

I have a companion video tutorial that shows even more cool automation stuff (plus mistakes to avoid).

Automate Excel with R Tutorial

Here’s an Excel File you’re going to make in this tutorial from R. 👇

Step 1: Collect, Prepare Data & Plots

First, collect your data using tidyquant, an awesome package for importing & working with Financial Data.

Get the code.

Pivot Table Data

The previous code makes this Pivot Table (stock returns by year and symbol).

Time Series Stock Performance Plots

Here’s the stock performance plot from the previous code.

Step 2: Automate R to Excel

Now for the magic - Let’s add the Pivot Table & Stock Plot to Excel! 🔥

1. Initialize a workbook
2. Create a worksheet
3. Add the stock plot
4. Add the Pivot Table
5. Save the workbook
6. Open the workbook (programmatically)

Get the code.

Excel Output (.xlsx)

You’ve just automated creation of an Excel Workbook with R.

💡 Conclusions

You learned how to use the openxlsx library to create automated reports using R and Excel together. Great work! But, there’s a lot more to becoming a Business Scientist.

If you’d like to become a Business Scientist (and have an awesome career, improve your quality of life, enjoy your job, and all the fun that comes along), then I can help with that.

Step 1: Watch my Free 40-Minute Webinar

Learning data science on your own is hard. I know because IT TOOK ME 5-YEARS to feel confident.

AND, I don’t want it to take that long for you.

So, I put together a FREE 40-minute webinar (a masterclass) that provides a roadmap for what worked for me.

Literally 5-years of learning, consolidated into 40-minutes. It’s jammed packed with value. I wish I saw this when I was starting… It would have made a huge difference.

Step 2: Take action

For my action-takers, if you are ready to become a Business Scientist, then read on.

If you need take your skills to the next level and DON’T want to wait 5-years to learn data science for business, AND you want a career you love that earns you $100,000+ salary (plus bonuses), AND you’d like someone to help you do this in UNDER 6-MONTHS or less….

Then I can help with that too.

Surprise!

There’s a link in the FREE 40-minute webinar for a special price (because you are special!) and taking that action will kickstart your journey with me in your corner.

Get ready. The ride is wild. And the destination is AMAZING!

Join My FREE 40-Minute R Webinar (become a 6-figure data scientist)