Excel 2010: Retain changing cell values

[John19]

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.

 

[oldbrewer]

you need a macro activated by the updating process that takes THIS dollar rate say and goes to another location where last 3 were displayed, delete the oldest, move the other 2 up, and put new dollar rate at bottom, do the trend searching on these numbers and paste the flag back if appropriate...

 

[John19]

you need a macro activated by the updating process that takes THIS dollar rate say and goes to another location where last 3 were displayed, delete the oldest, move the other 2 up, and put new dollar rate at bottom, do the trend searching on these numbers and paste the flag back if appropriate...

I'm bit confused, is there a way for me to attach my excel file in this forum? would be easier for you to see what i'm trying to do

 

[John19]

The percentage figures are refreshed every minute and so are overwritten - I have to keep a track of the last 3 changes constantly.

 

[John19]

I have also posted this question at these sites:


http://www.excelguru.ca/forums/showt...ng-cell-values
http://www.excelforum.com/excel-gene...web-query.html
http://www.mrexcel.com/forum/excel-q...ll-values.html
http://www.excelkey.com/forum/viewto...p=20637#p20637
Excel 2010: Retain changing cell values - web quer

 

[oldbrewer]

you get a new dollar rate, somewhere you have the last 3 rates stored, delete the oldest and add the new rate to the bottom and do your testing for trends there reporting findings to where you want - this is a suggestion for how to tackle it

a1 was 5 then 7 then 9 set G1=5 G2= 7 G3 = 9

now A1 becomes 15
delete G1 allowing G2 and G3 to become G1 and G2
put A1 value into G3

do trend analysis in cell H3

A1 becomes 99
delete G1 allowing G2 and G3 to become G1 and G2
put A1 value into G3

do trend analysis in cell H3