How to create a rolling data feed?

[btsiliacos]

Hi ExcelForum,

I am pulling external data from Google Big Query into a sheet, but the problem is I don’t have the option to append new rows to data source upon refresh.This can become very costly since I have to keep pulling the previous month with the current month data in order for it not to be overwritten.

Is there a way to create a rolling data feed using VBA?

Any insight would be appreciated.

[mehmetcik]

I would pull your new data into a temporary sheet or into an unused area of your sheet

Then I would cut insert the new data above your historical data.

[dflak]

Here is a bit of code that may help. It appends table 2 (in this case current monthly data) to the end of table 3 (Historical data)

Please Login or Register  to view this content.

[btsiliacos]

Hey dflak,

Thanks for taking the time to answer and provide a solution. So I understand correctly, each day table 2 is updated and new rows are inserted, this code will take the new rows and append them to the historical data in table 3? Also, how would I execute this?

[btsiliacos]

Hey dflak,

Thanks for taking the time to answer and provide a solution. So I understand correctly, each day table 2 is updated and new rows are insterted, this code will take the new rows and append them to the historical data in table 3? Also, how would I execute this?

[dflak]

Open the VB Editor by pressing ALT-F11. Select Insert -> Module. Copy and paste the code below into the module. Type in your own table names where it says "Table_to_Append_To" and "Table_to_Append_From." Use the quotes around the table names.

You can execute the code by going to View -> Macro and run it from there or you can also use the Options menu there to assign a CTRL Key to run the macro.

Please Login or Register  to view this content.

[btsiliacos]

Hi Dflak,

Unfortunately I am getting a compile error - variable not defined. So I went ahead and named the variable and declared range as its data type. However, when I attempt to execute I get another error that states "Expected: indentifier or bracketed expressions for the row right above "End Sub". Any thoughts?

Please Login or Register  to view this content.

[dflak]

I think it's time to see a sample workbook so we can work with a concrete example.

[btsiliacos]

Sure thing. Here is the attached workbook. Sheet 1 is where I run a query that fetches data from Google Big Query and dumps it in sheet 2. Ideally this query will run every hour or so in the background and sheet 3 will continue to update with new rows.

I couldn't attach it to this chat for some reason so I uploaded the workbook through scribd - https://www.scribd.com/document/333757743/Example-1

If you have an alternative way for me to upload this, let me know.

[dflak]

Please attach to this forum. I don't have a login for the site you uploaded to.

[dflak]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[btsiliacos]

Attaching the spreadsheet again per your instruction. Sheet 1 executes a query in Column B and fetches the data from Google Big Query. Notice in column B8 how it is only pulling data from December 1st and onwards. The output is dumped into sheet 2.

Once the month ends, I update B8 with the new month (2017-01-01) and execute the query again. Problem is, rather than inserting new rows for the new month, it will completely override last months data. As a work around, I have to manually copy and paste it into a master sheet to preserve the data history.

Ideally the query in sheet 1 will execute every hour in the background and sheet 3 will continue to update with new rows as the day progresses.

[dflak]

Ok, a couple of things: these are not tables, they are data ranges. Secondly, both Sheet2 and Sheet3 are identical and both have a data connection. Which is the daily and which is the history? I other words, what gets joined to what?

I strongly suggest that you convert these data ranges to excel tables otherwise you will constantly be revising formulas that reference them or you will reference a million rows of data when you only need 40.

How are you importing your data into Excel? Most methods I'm aware of put data into Excel tables.

[btsiliacos]

Sorry for the confusion. Sheet 2 is daily and Sheet 3 is history. The data is being imported through Google BigQuery's Connector app called connector.igy. When the file is selcted, it gives me series of prompts to configure the query such as output of data (which I've chosen sheet 2), query itself (sheet 1 column B5:B13), and project ID (column B1). Unfortunately it looks like it only imports it as a range not a table. Will that be a problem?

[dflak]

It is not a big deal that the ranges are not data. Once you have data established in the history page, you can convert that range to a table if you wish. It doesn't matter for the purposes of this program - but if you have pivot tables or formulas that reference the history sheet, tables are easier to work with.

[dflak]

It doesn't matter that these are not Excel tables. However, once you have data in the history sheet, you can convert it to an Excel table. It makes no difference to this code.

[btsiliacos]

Yup, I will have pivot tables references the history sheet. So, how would I use your vba code to update new rows in sheet 3 (history) from sheet 2 (daily) without it overriding?

EDIT: Just saw your attached file - it didn't appear right away.

[dflak]

Had some trouble posting.

The code appends to the history file - it doesn't do any updating.

[btsiliacos]

This is exactly what I had in mind. Dflak, thank you for your help. Do I have to run the macro manually each time for it to append to the history file? I'd prefer it to trigger every time the daily sheet updates. Also, each daily sheet represents one partner and in reality I'll have 5. I am assuming I'd duplicate the module and specify the name of the new tables?

[dflak]

I am not quite sure what you mean by 5 partners. Do you mean that there will be 5 daily sheets? If so, there will have to be some modification to the code.

What I suggest you do is record a macro to do the data update and then modify the code. The primary purpose of this macro would be to import the data - something you are doing now manually, but you can then call this code from the macro after the data import.

[btsiliacos]

Correct, there would be 5 daily sheets for now but that could grow. How can the code be modified to include this?

[dflak]

Here you go. On the Parameters Sheet, Cell B1 is the sheet name you want to copy to. Also in column B is a table that contains the names of the sheets you want to copy from. Modify this as you need. As you add or delete sheets to this list, the code will adjust to accommodate them. There will be no changes needed in the code.