web query question

adinic

New Member
Joined
Jul 5, 2006
Messages
12
Hello! I have the following problem: I import some data from a website every day. How can i work with the old data and the new data. Exemple:
new data-old data. Let's say the new data is "200" and the old data is "150" the result should be 50. Is that possible? thank you! If you have any question i would be glad to answer them. Thank you for your help!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Yes, it is possible. The trick is to copy the values out of your web query range to a "storage" location so you can accumulate data. Are you comfortable working with macros?

I've built several workbooks that do this. One is a sheet that follows the top 100 scores of a group of contestants. The data is formatted as a column of 100 names and another column of 100 scores. My macro looks at another sheet, finds the first empty column, and copies the values (not the formulas) to that empty column. In this way I get an expanding set of data week after week. To compare just yesterday vs. today, you could set up the macro to move data from a "Today" column to a "Yesterday" column, then put today's new data in the "Today" column.

Did this make sense? If you give us more idea of how your data is formatted, I (or someone else on the board) can write a macro for you.
 
Upvote 0
My data is taken from a stock market website. I don't exactly understand what you mean when you say "how your data is formatted". If you mean what i think you mean, then the cells are in general format.
 
Upvote 0
By "format" I meant how many rows, how many columns, etc. Here's a sample I quickly mashed up, using Yahoo Finance quote for Google. The webquery lives in cells A2:B18. The name and ticker symbol I typed in manually.
Book1
ABCDEFGHI
1GoogleGOOGGoogleGOOG
2Last Trade:515Last Trade:515
3Trade Time:24-AugTrade Time:24-Aug
4Change:Up 2.81 (0.55%)Change:Up 2.81 (0.55%)
5Prev Close:512.19Prev Close:512.19
6Open:512.61Open:512.61
7Bid:501.50 x 100Bid:501.50 x 100
8Ask:N/AAsk:N/A
91y Target Est:604.661y Target Est:604.66
10
11Day's Range:508.50 - 515.55Day's Range:508.50 - 515.55
1252wk Range:376.72 - 558.5852wk Range:376.72 - 558.58
13Volume:2,473,013Volume:2,473
14Avg Vol (3m):5,273,490Avg Vol (3m):5,273,490
15Market Cap:160.75BMarket Cap:160.75B
16P/E (ttm):41.85P/E (ttm):41.85
17EPS (ttm):12.31EPS (ttm):12.31
18Div & Yield:N/A (N/A)Div & Yield:N/A (N/A)
19
20TodayTodayYesterdayYesterday
Sheet1


Here's the code to take the data in columns A and B (labeled "Today" at the bottom), copy the values to columns H:I (labeled "Yesterday" at the bottom), and re-query the website to get new "Today" data:

Code:
Sub Shift_Data()
    
    Range("H1:I18").Value = Range("A1:B18").Value
    Range("A2").QueryTable.Refresh BackgroundQuery:=False
    
End Sub

You can built a button on the sheet to initiate the code, you can create a routine to do the code for you at a specific time of day, or when you open the workbook. Lots of flexibility.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top