Web Data Query: Automatically Add or Append New Web Query Data to Previous Data

[krismiller1982]

I'm still struggling to find a solution after several months of searching for an answer. Surely this concept is simple. Yes?

I have setup a web query to grab information from Yahoo Finance ( STOCKHISTORY function does not include adjusted stock prices, so I must use Yahoo Finance ). I'm trying to accomplish the following:

Upon web query refresh, new data is added or appended to the previously queried data. Thus automatically adding new rows of daily data into the table. Is there a way to do this? I appreciate anyones help. Thank you.

 

[Kerryx]

try this setting under Table design

 

[krismiller1982]

try this setting under Table design
View attachment 74607

Doesnt work.Thats the default setting btw. Thank you for trying to help though.

 

[alansidman]

Need more information. ie. Web site you are querying. Full URL. You don't indicate how you are running your web query, VBA? Power Query? Some other method. Help us help you. More detailed information.

 

[krismiller1982]

Need more information. ie. Web site you are querying. Full URL. You don't indicate how you are running your web query, VBA? Power Query? Some other method. Help us help you. More detailed information.

My apologies for lack of info. I'm using "Get Data from Web". I use the Power Query editor to transform certain elements of the table. But I'm open to using VBA if its more elegant. The webpage I use for the query is SPDR S&P 500 ETF Trust (SPY) Stock Historical Prices & Data - Yahoo Finance. When Excel grabs data from that URL, it populates the most recent 100 market days. I'm trying to find a way for new daily data to automatically append to the older data upon refreshing while not losing the older data..... I'm adding a quick screen shot to show how the data looks if that helps. I appreciate your time & help.

 

[John_w]

You could do this with a VBA class which handles the AfterRefresh event on the web query. When the refresh occurs it copies new rows from the web query table to another table which contains all the historic data rows.

 

[krismiller1982]

You could do this with a VBA class which handles the AfterRefresh event on the web query. When the refresh occurs it copies new rows from the web query table to another table which contains all the historic data rows.

Thank you. I'll look into how I can do that. I know nothing about VBA, but will search.

 

[krismiller1982]

After searching I found a solution. However I would like to learn how to do this using VBA. I'll update the answer once I find it.

"In the interest of keeping the solution with the question in case those websites are no longer accessible, the basic premise of incremental updates in the Excel for Windows version of Power Query is as follows:

1. Create your initial PQ with the transforms that you want and output to an Excel Table.
2. Create a second PQ referencing the Excel Table you just created, and set it as “Connection Only”. This preserves the existing data.
3. Return to edit the PQ you created in Step 1 by adding a step to append the output of the second PQ. This runs the first query to get more recent data, then appends the existing data that you preserved in Step 2 onto that result for the finished output.
4. Add steps to the first query after that to de-dupe and sort the results, if necessary. This will obviously require some kind of unique identifier on each record (a unique date/time, an ‘id’ field, whatever provides some unique value). You can always remove that unique id column after de-duping as a last step if you don’t want to see it in the final output."

 

[John_w]

1. Create your initial PQ with the transforms that you want and output to an Excel Table.
2. Create a second PQ referencing the Excel Table you just created, and set it as “Connection Only”. This preserves the existing data.
3. Return to edit the PQ you created in Step 1 by adding a step to append the output of the second PQ. This runs the first query to get more recent data, then appends the existing data that you preserved in Step 2 onto that result for the finished output.
4. Add steps to the first query after that to de-dupe and sort the results, if necessary. This will obviously require some kind of unique identifier on each record (a unique date/time, an ‘id’ field, whatever provides some unique value). You can always remove that unique id column after de-duping as a last step if you don’t want to see it in the final output."

A very good solution and certainly far simpler than using VBA!

In Step 2 for the second PQ, the Date column contains dates and times as 00:00:00. Change the Data Type from 'Date/Time' to 'Date' so that the dates are in the same format as the first PQ, thereby allowing duplicates to be removed in Step 4.

 

[sverrealbech]

eb". I use the Power Query editor to transform certain elements of the table. But I'm open to using VBA if its more elegant. The webpage I use for the query is SPDR S&P 500 ETF Trust (SPY) Stock Historical Prices & Data - Yahoo Finance. When Excel grabs data from that URL, it populates the most recent 100 market

I'm still struggling to find a solution after several months of searching for an answer. Surely this concept is simple. Yes?

I have setup a web query to grab information from Yahoo Finance ( STOCKHISTORY function does not include adjusted stock prices, so I must use Yahoo Finance ). I'm trying to accomplish the following:

Upon web query refresh, new data is added or appended to the previously queried data. Thus automatically adding new rows of daily data into the table. Is there a way to do this? I appreciate anyones help. Thank you.

Hi krismiller1982,
Did you find a solution using VBA? Please upload an excel-sheet if you did. Thx!