Excel 2010: Retain changing cell values - web query

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)

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

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

see excel sheet and screenshot


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
http://www.ozgrid.com/forum/showthread.php?t=192362

Hey John,

Can you post this file so that we can test out possible solutions working with the moving data. If you have sensitive data in this file, obviously I would recommend scrubbing it of that or moving the querry to a sample workbook before uploading.

To upload Go Advanced and click the icon in the form toolbox that looks like paper with a paperclip. You can also hit ALT+X to go Advanced.

You may also want to add a static sheet and label it results or after showing what you wish to see as a final results. This one obviously should be values and not ever changing with the query so that we can see scenarios and your end expected results. While I think your Post clarifies what you want, visuals are always helpful to ensure there are no assumptions.

Cheers