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.
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.
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.
Time needed: 10 minutes.
Step-Step Tutorial to Import Live Data from Google Sheets into Excel
- 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:
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)
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.
Take Your Excel Trading and Backtests to the Next Level
Make more use of Excel by getting your own powerful backtest model.