Excel 2010: Retain changing cell values

J

John19

New member
Joined
Dec 20, 2014
Messages
14
Reaction score
0
Points
0
I have a web query updated every 1 minute and returns live currency changes. It’s displayed in a column containing 127 currencies and against each, the percentage change (+/-) is shown.
e.g

GBP/USD +0.15%.
EUR/USD -0.30%

What I need is to display in another column for each currency, a “+” or “-“ sign, only if the last 3 consecutive changes were + or - , if not leave blank.

My aim is to indicate the direction each currency is going (either + or -).

The main issue is, at every minute updates, figures are refreshed and I can’t find a way for excel to store the last 3 changes (NOT JUST UPDATES BUT ACTUAL CONSECUTIVE CHANGES FOR EACH CURRENCY).

I spent a whole month of Sundays reading excel questions online and still going nowhere.
 
Prior to the web query update, have your initiating procedure write what you want to preserve to another column, for example, column Z. Just before this write, shift columns Y and Z to X and Y, now after the write, columns X, Y and Z will be the last 3 changes.

This example uses column J starting at row 5 for whats to be preserved.

Code: 
With Sheet1
    'shift existing range over 1 column to the left
    arr = .Cells(5, 25).Resize(127, 2)
    .Cells(5, 24).Resize(127, 2).Value = arr
    'copy what's to be preserved before update
    .Range("Z5").Resize(127, 1).Value = .Range("J5").Resize(127, 1).Value
End With
 
this is the excel sheet showing how far i've got to - just need to figure out how to complete the colums 1 & 2 showing previous currency changes
 

Attachments

  • Currency.xlsx
    13.3 KB · Views: 16
where would i insert this code? please take a look at what i've done so far - attachment currency

thank you
 
see my screenshot
 

Attachments

  • screenshot.png
    screenshot.png
    35.7 KB · Views: 13
Must confess, I don't know, yet.
I had hoped it was a macro initiating the query, but with the .xlsx file I'll have to wait and see what others suggest.
We will both be learning something here.
 
see excel file and screenshot

I have a web query updated every 1 minute and returns live currency changes. It’s displayed in a column containing 127 currencies and against each, the percentage change (+/-) is shown, e.g

GBP/USD +0.15%.
EUR/USD -0.30%

What I need is to display in another column for each currency, a “+” or “-“ sign, only if the last 3 consecutive changes were + or - , if not leave blank.

I’m trying to indicate the direction each currency is going (either + or -).

The main issues is, at every minute updates, figures are refreshed and I can’t find a way for excel to store the last 3 changes (NOT JUST UPDATES BUT ACTUAL CONSECUTIVE CHANGES FOR EACH CURRENCY)

I spent a whole month of evenings reading excel questions online and still could not find anything.
 

Attachments

  • Currency.xlsx
    12.7 KB · Views: 14
  • screenshot.png
    screenshot.png
    31.6 KB · Views: 12
The percentage figures are refreshed every minute and so are overwritten - I have to keep a track of the last 3 changes constantly.
 
Is there anything on your sheet that this web query is guaranteed to change every update?
If so, what is it and what cell is it in?

I'm thinking a macro could be run via the Worksheet_change event, except that it won't fire on changes resulting from formulas or automation, so looking for something that could be used to consistently manipulate something else to have an event fire.

Wait a minute, Have a look at this. I currently see your question on 5 different forums, are there more?

http://www.excelguru.ca/forums/showthread.php?3892-Excel-2010-Retain-changing-cell-values
http://www.excelforum.com/excel-gen...10-retain-changing-cell-values-web-query.html
http://www.mrexcel.com/forum/excel-questions/825321-excel-2010-retain-changing-cell-values.html
http://www.excelkey.com/forum/viewtopic.php?f=3&p=20637#p20637
http://www.ozgrid.com/forum/showthread.php?t=192362
 
NoS said:
Is there anything on your sheet that this web query is guaranteed to change every update?
If so, what is it and what cell is it in?

I'm thinking a macro could be run via the Worksheet_change event, except that it won't fire on changes resulting from formulas or automation, so looking for something that could be used to consistently manipulate something else to have an event fire.

Wait a minute, Have a look at this. I currently see your question on 5 different forums, are there more?

http://www.excelguru.ca/forums/showthread.php?3892-Excel-2010-Retain-changing-cell-values
http://www.excelforum.com/excel-gen...10-retain-changing-cell-values-web-query.html
http://www.mrexcel.com/forum/excel-questions/825321-excel-2010-retain-changing-cell-values.html
http://www.excelkey.com/forum/viewtopic.php?f=3&p=20637#p20637
http://www.ozgrid.com/forum/showthread.php?t=192362
Click to expand...


Sorry didn't know - won't happen again
 
You need to go to all other sites and post links there too.
 
Questions:

1.) Is there anything on your sheet that is guaranteed to change every update?
Maybe a time or date or something else? If so, what is it and what cell is it in?

2.) Is the sheet your web query updates named "Sheet1" or has the tab name been changed?

3.) The sheet you posted has the Chg.% starting in D7 and going down for 28 rows. Is it correct to think your real data starts in D7 and goes down for 127 rows?

4.) The last 3 consecutive changes, does that mean the 3 changes prior to the current one or is the current one considered the third?
 
You must log in or register to reply here.
Back
Top