How to Import Live Data From Google Sheets into Excel

Google Sheets into Excel

Do you want to get live Google Sheets data into your desktop Excel files?

Google Sheets or Excel – Which is Better?

I use Google Sheets and Excel every day. They are both powerful productivity tools that everyone should be familiar with.

Google Sheets

A purely web-based system. It is permanently connected to the internet with direct access to the Google Finance database.

Google Sheets has an evergrowing number of plugins that you can use to improve the basic service.

You can update and check your spreadsheets from your phone when you are away from your desk.

Excel

Desktop based. It can handle very large files, and is often faster than Sheets.

You can work on files offline and has a greater number of functions than Sheets.

But you do not have to choose. In this article, I am going to show you how to get the best of both worlds.

Connecting Sheets and Excel

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

This data is continuously updating, and I want a way 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

Time needed: 10 minutes.

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

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

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

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

Take Your Excel Trading and Backtests to the Next Level

Make more use of Excel by getting your own powerful backtest model.

Leave a reply and let me know what you think