How to Import Live Data From Google Sheets into Excel

Last Updated on September 22, 2023 by Mark Ursell

This article shows you how to import live data from Google Sheets into Excel.

Video Tutorial – Import live data from Google Sheets into Excel

Watch me demonstrate in this video.

Are you ready to unlock the true potential of two spreadsheet titans?

Harnessing the dynamic features of both Google Sheets and Excel can pave the way for improvements in workflow and productivity. In this guide, I demonstrate a technique that allows you to import live data from Google Sheets directly into Excel, creating a powerful synergy between these two platforms.

By seamlessly integrating the real-time prowess of Google Sheets with the analytical might of Excel, you’ll help to manage your trading strategies, conduct market analysis, and unleash your data collection insights.

Let’s dive in and discover how you can supercharge your workflow by bridging the gap between Google Sheets and Excel.

Google Sheets or Excel – Which is Better?

Firstly, let’s look at the strengths and weaknesses of Excel and Google Sheets.

As a seasoned user, I rely on Google Sheets and Excel daily. They are both powerful productivity tools that almost everyone is familiar with. They both have an extensive ecosystem of tools and plugins that combine with them.

But I am only interested in the parts that are directly useful to me. And in this article, I will guide you through the process of merging the best aspects of both platforms.

Google Sheets

I use Google Sheets to run my live Trading Strategies.

• A purely web-based system. It is permanently connected to the internet with direct access to the Google Finance database.
• Google Finance has a large amount of financial data available. You can get both historical and real-time live data using formulas.
• Google Sheets has an ever-growing number of plugins that you can use to improve the basic service.
• You can program your trading strategies into Sheets and then check your positions, trading signals and P/L from your phone when you are away from your desk.

Excel

I use desktop Excel for Backtesting My Trading Strategies and Market Analysis.

• Excel is desktop based. It can handle very large files and is often faster than Sheets which runs in your browser.
• You can work on files offlinem which can be an advantage for backtesting.
• Excel has a lot of different plugins that can improve the basic features.
• Excel has a greater number of functions and more overall functionality than Sheets.

Why Connect Excel and Google Sheets to get price data?

Connecting Sheets and Excel

As a trader, I love using the =GOOGLEFINANCE function. It gives me access to a vast amount of price, volume and other financial data.

This data is continuously updating, and I want to get the latest prices into my Excel spreadsheets.

If you want to learn more about how I use Google Sheets to manage my trading strategies, check out this article: How to Store Live Price Data In Google Sheets

You can learn more about using the GOOGLEFINANCE formula here: GOOGLEFINANCE Formula

Tutorial

Step-Step Tutorial to Import Live Data from Google Sheets into Excel

Total Time: 10 minutes

Create Your Google Sheet

In this example, I am using the function =GOOGLEFINANCE

This function automatically imports the latest price and other stock and financial data into your spreadsheet. This is an incredibly useful function, and you learn more about it here: Using the Google Finance Function.

I have set up my sheet using the formula to get the Dow Jones prices for the last 30 days. The formula I used to get the Dow is:
=googlefinance(“.dji”,”all”,today()-30,today())

Watch the video to see me demonstrate this.

Publish Your Google Sheet to the Web

You now need to publish the document to the web.

Click File – Publish to the web.

Select Link and choose the sheets you want to publish.

From the dropdown menu select (.csv)

Click Publish

Copy the highlighted link.

Import the Data Into Excel

In Excel, click on the Data tab and select From Web.

Paste the URL you copied from Google Finance into the box.

Click OK, and when the data appears, click Load.

If you want the data to update regularly, select the query click Data and then click the Refresh All dropdown arrow. Click Connection Properties and choose how often you want the data to refresh.

The Next Step

Congratulations! You now have the knowledge and tools to bridge the gap between Google Sheets and Excel.

Remember, Google Sheets offers a web-based system with easy access to the Google Finance database, while Excel provides a desktop-based powerhouse with extensive functionality and offline capabilities. By leveraging the =GOOGLEFINANCE function and importing live data from Google Sheets into Excel, you can stay updated with the latest prices, volume, and other financial data that drive your trading decisions.

To learn more about managing your trading strategies using Google Sheets, check out my article on How to Store Live Price Data in Google Sheets.