Many of us gather and analyze data in spreadsheets and there are multiple ways to import data into our files. Both Excel and Google Sheets have formulas that allow you to import live stocks and shares data into your spreadsheet, for example. But what if you want to keep a historical track of stock prices and monitor their evolution on the stock exchange? There’s no inbuilt spreadsheet function that allows you to “capture” volatile and fluctuating data values for future reference. If you want to analyze historical changes over time, you need reliable records of past data.
Saving different versions of a file at regular intervals is one option, but that’s incredibly time-consuming and you will quickly generate a vast number of files. An alternative is to append automatically imported values in your Excel workbook, generating a historical log of changes to data. This allows you to:
- Track trends and data changes over time
- Keep a record of old data entries from specific points in time
- Generate automated dashboards, reports and charts
- Build an automated workflow that pulls fresh data from multiple Excel workbook(s) into a central spreadsheet
How to append Excel data automatically:
Step 1: Install Sheetgo
Click +Create workflow and then Connect to create your first connection.
At the top of the screen, give this Untitled Workflow a name so you can identify it at a later date.
Step 2: Select Excel as your data source
Choose your source file from inside your cloud storage. Your source file is the Excel workbook containing the dynamic data that changes frequently. Sheetgo will send a copy of this data to another tab(worksheet) at regular intervals, appending data in a historical log.
Are your files stored on your computer?
If you want to connect files that are stored locally (on your computer) you can set up an automated system to back up and sync files from your desktop to your online cloud storage service.
This enables you to create automated data flows using Sheetgo. It also keeps your files secure and allows you to access them from anywhere. Learn more.
Now select your source tab. This is the tab(worksheet) containing the data you want to transfer.
Sheetgo works by sending data from one tab(worksheet) to another. Note that you can send data to a tab(worksheet) in the same workbook or another workbook. Your source tab remains unchanged.
Let’s take a look at an example. Here I have an Excel workbook (“Historic Stock data”) containing a tab(worksheet) called “Tab 1”.
Tab 1 contains a live link to the Nasdaq exchange database, which includes information from all the major U.S. stock indexes.
Step 3: Choose your destination file
Under Send data to, select your destination file. This is the file you want to transfer the data to. In this case, this is the spreadsheet where exported data entries will be be appended in a log format.
You can send the data to:
- The same file
- An existing spreadsheet (Excel or another file format) from any of your cloud storage solutions
- A new spreadsheet (Excel or another file format). Sheetgo will create a new file for you automatically. Just choose which cloud storage folder you want it to be saved in.
In all cases, Sheetgo will send the data to a new tab that will appear in the destination file.
In this example I want to create the historic log in the same workbook, in a new worksheet.
Therefore I select Existing file and select the same spreadsheet (my source file – Historic Stock data) as my destination file.
I give the new tab the name “Today’s stock prices”.
Step 4: Enable the Append setting
Click on Settings, under the New File Tab box.
Enable Append by sliding the button to the right.
Click Finish and save to create the Append connection. Sheetgo will now start transferring your data.
Your workflow has now been created. Check out your destination file — you will see it contains the imported data.
Every time the connection between the files is updated, a fresh data entry will appear in the destination tab. Subsequent entries will be appended in a log.
To update the connection manually, click Run on the floating workflow menu.
Step 5: Automate the workflow
You can now automate this process to generate an automated log of data changes over time. The workflow will run automatically, appending fresh data in the destination sheet without you having to open Sheetgo or any of your spreadsheets.
Click Automate on the floating menu bar of your workflow. Think about how often your source data will change and how frequently you want to record the data. You can schedule updates from once an hour to once a month.
Note that if you schedule updates more frequently than the data changes, you may see repeated (identical) entries in the destination spreadsheet.
In this example, I want to track changes to stocks and share prices every day. Therefore I choose Daily updates between midnight and 1am in my time zone.
Transferring time-specific data
Tip: If your source sheet has a “date” column, try using Append together with Sheetgo’s Filter. This will enable you to transfer specific data related to a period of time, such as the current day or month.
Automated data collection and reporting in Excel
That’s how to append Excel data and generate an automated historical record.
With your first connection you have started to build an automated workflow. You can expand on the workflow by adding more connections to other files, importing more source data from other spreadsheets or sending data to reports and dashboards.
Looking for more spreadsheet-based automation ideas? Learn how to combine data from multiple Excel workbooks into one central file.