# 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

# SPECIAL ANNOUNCEMENT: ChatGPT for Data Scientists Workshop on December 13th

Inside the workshop Iâ€™ll share how I built a Machine Learning Powered Production Shiny App with ChatGPT (extends this data analysis to an insane production app):

What: ChatGPT for Data Scientists

When: Wednesday December 13th, 2pm EST

How It Will Help You: Whether you are new to data science or are an expert, ChatGPT is changing the game. Thereâ€™s a ton of hype. But how can ChatGPT actually help you become a better data scientist and help you stand out in your career? Iâ€™ll show you inside my free chatgpt for data scientists workshop.

Price: Does Free sound good?

How To Join: ðŸ‘‰ Register Here

# R-Tips Weekly

Here are the links to get set up. ðŸ‘‡

# 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.

### 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
5. Save the workbook
6. Open the workbook (programmatically)

### 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.

# Struggling to become a data scientist?

You know the feeling. Being unhappy with your current job.

Promotions arenâ€™t happening. Youâ€™re stuck. Feeling Hopeless. Confusedâ€¦

And youâ€™re praying that the next job interview will go better than the last 12â€¦

â€¦ But you know it wonâ€™t. Not unless you take control of your career.

The good news isâ€¦

Iâ€™ve helped 6,107+ students learn data science for business from an elite business consultantâ€™s perspective.

Iâ€™ve worked with Fortune 500 companies like S&P Global, Apple, MRM McCann, and more.

And I built a training program that gets my students life-changing data science careers (donâ€™t believe me? see my testimonials here):

# Whenever you are ready, hereâ€™s the system they are taking:

Hereâ€™s the system that has gotten aspiring data scientists, career transitioners, and life long learners data science jobs and promotionsâ€¦

P.S. - Samantha landed her NEW Data Science R Developer job at CVS Health (Fortune 500). This could be you.