Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Excel 2010: Retain changing cell values

  1. #1

    Excel 2010: Retain changing cell values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a web query updated every 1 minute and returns live currency changes. Its 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 cant 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.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  3. #3
    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
    Attached Files Attached Files

  4. #4
    where would i insert this code? please take a look at what i've done so far - attachment currency

    thank you

  5. #5
    see my screenshot
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	screenshot.png 
Views:	6 
Size:	35.7 KB 
ID:	2977  

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  7. #7

    see excel file and screenshot

    I have a web query updated every 1 minute and returns live currency changes. Its 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.

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

    The main issues is, at every minute updates, figures are refreshed and I cant 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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	screenshot.png 
Views:	4 
Size:	31.6 KB 
ID:	2981  
    Attached Files Attached Files

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

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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/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

  10. #10
    Quote Originally Posted by NoS View Post
    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/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

    Sorry didn't know - won't happen again

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •