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?
- Why Connect Excel and Google Sheets to get price data?
- Connecting Sheets and Excel
- Video Tutorial – Import live data from Google Sheets into Excel
- The Next Step: Backtest Your Strategy
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.
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.
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
Video Tutorial – Import live data from Google Sheets into Excel
Watch me demonstrate in this video.
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:
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.
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.