How to Import Live Data From Google Sheets into Excel

Last Updated on November 28, 2022 by Mark Ursell

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

Google Sheets or Excel – Which is Better?

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

I use both Google Sheets and Excel almost every day. They are both powerful productivity tools that almost everyone is familiar with. They both have an extensive eco-system 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 am going to show you how to get the best of both worlds by linking Google Sheets with Excel.

Google Sheets

I use Google Sheets to run my live Trading Strategies.

How to import live data from google sheets into excel - sample Google Sheets Trading Strategy
  • 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.

Tradinformed Excel Backtest Model Results Page Screenshot
  • 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

Video Tutorial – Import live data from Google Sheets into Excel

Watch me demonstrate in this video.

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.

Step 1 - Create your Google Sheet

Publish Your Google Sheet to the Web

You now need to publish the document to the web.

Click FilePublish 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.Step 2 - Publish to the web

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.

Step 3 - Import into Excel

The Next Step: Backtest Your Strategy

I do all my backtesting in Excel. Over many years I have developed a backtesting system that is easy to use for anyone familiar with Excel.

You can use it to test everything from simple investment ideas to complex quantitative strategies.

Tradinformed Backtest Models help you test your strategies, develop new strategies, and find the best settings.

Tradinformed Members get:

  • A new backtest model, including my results and analysis every month.
  • Direct access to me through the Members Webinars.
  • Exclusive tutorials so that you can quickly understand and use the models.