IF x THEN Paste Value?

laleph · May 2, 2011

I'm currently trying to create an investment portfolio tracker, however I'm having some trouble tracking my data on a day to day basis.

Right now I have a section for my portfolio which imports external data for share prices, which then calculates change in value, and my overall portfolio value. Getting my portfolio value on any given day is rather easy, however I'm running into trouble trying to automatically track it's value.

Currently I have 3 columns, Date (A), Value (B), and Pasted Value (C). Date lists every day (May 2, May 3, May 4... etc). Value uses the VLOOKUP function and says "IF external data date = Date in column A, then return portfolio value."

Up to this point I'm good. I run into problems when all of a sudden we get to the next day (Say... May 3rd), and suddenly we lose the portfolio value from today (May 2nd) because the date of the external data no longer = May 2nd.

What I want to do is paste the value of Column B into column C, so any time in the future, an old value of the portfolio on any given day is accessible. I realize I could do this manually, however I'd like this step to be completed automatically.

Thank you SO much for you help, and feel free to ask me if you have any questions.

John Davis · May 2, 2011

laleph said:
I'm currently trying to create an investment portfolio tracker, however I'm having some trouble tracking my data on a day to day basis.

Right now I have a section for my portfolio which imports external data for share prices, which then calculates change in value, and my overall portfolio value. Getting my portfolio value on any given day is rather easy, however I'm running into trouble trying to automatically track it's value.

Currently I have 3 columns, Date (A), Value (B), and Pasted Value (C). Date lists every day (May 2, May 3, May 4... etc). Value uses the VLOOKUP function and says "IF external data date = Date in column A, then return portfolio value."

Up to this point I'm good. I run into problems when all of a sudden we get to the next day (Say... May 3rd), and suddenly we lose the portfolio value from today (May 2nd) because the date of the external data no longer = May 2nd.

What I want to do is paste the value of Column B into column C, so any time in the future, an old value of the portfolio on any given day is accessible. I realize I could do this manually, however I'd like this step to be completed automatically.

Thank you SO much for you help, and feel free to ask me if you have any questions.

Does this help any?

Code:

Sub laleph()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = lr To 2 Step -1

    Range("C" & i).Value = Range("B" & i).Value
    
Next i

End Sub

laleph · May 2, 2011

It does a great job of pasting values to column C, however when I run the macro again (on another day for example). I want the old value to remain. So if my portfolio was worth $112,500 yesterday, and today it calculates my portfolio to be worth $113,000, I want to see both yesterday's value, and today's value in column C. That is... I don't wany the values of any past dates to dissappear. Currently when I run the code, today's value will update perfectly, but yesterday's will dissappear.

Mefu · May 2, 2011

So ... you want to keep the values in column C if any ?
If yes you will have to modify John's line from the loop with

 Range("C" & i).Value = Range("C" & i).Value + Range("B" & i).Value

Or ... if you want to keep the values from yesterday maybe you will have to insert another column for today. In this way you will be able to do the math and compare with the previous day.

laleph · May 2, 2011

I want to keep all past values and update new values as they change. I modified John's loop as you described, however it still reacted the same way.

Maybe the best way of describing my situation is I have a column of dates, and a column of end-of-day stock prices. When I get my external data it only gets today's stock value (May 2nd), but when I update by external data tomorrow, there will be nothing for May 2nd, only May 3rd, hence forever losing May 2nd value. Once I have a value for May 2nd I want to paste its value into a column next to it, even when tomorrow, when it tries to pull external data for May 2nd, none will be there.

Does this make sense? Should I attach a file? Thanks again for your help!

-Lars

John Davis · May 3, 2011

laleph said:
I want to keep all past values and update new values as they change. I modified John's loop as you described, however it still reacted the same way.

Maybe the best way of describing my situation is I have a column of dates, and a column of end-of-day stock prices. When I get my external data it only gets today's stock value (May 2nd), but when I update by external data tomorrow, there will be nothing for May 2nd, only May 3rd, hence forever losing May 2nd value. Once I have a value for May 2nd I want to paste its value into a column next to it, even when tomorrow, when it tries to pull external data for May 2nd, none will be there.

Does this make sense? Should I attach a file? Thanks again for your help!

-Lars

Not sure I understand, but perhaps:

Code:

Sub laleph()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = lr To 2 Step -1

    If Range("A" & i) > Range("A" & i).Offset(-1, 0) Then Range("C" & i).Value = Range("B" & i).Value
    
Next i

End Sub

laleph · May 3, 2011

That still doesn't keep old data stored, it simply deletes and updates today's. I have attached an example spreadsheet. The left is what I have and am working with. The right is what I'd like the final product to look like. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
Thank you SO much for your help!
<o

></o

>
http://www.4shared.com/file/uofupWe7/Day_By_Day_Investment_Tracker_.html

-Lars

Mefu · May 3, 2011

Maybe you will want to import the data in the column D and keep at the beginning columns B and C for previous days.
Then brush the data and replace B with C and only if C<> D replace cell in C. Then delete column D.
In this way you you will have B and C to compare two days.

John Davis · May 3, 2011

laleph said:
That still doesn't keep old data stored, it simply deletes and updates today's. I have attached an example spreadsheet. The left is what I have and am working with. The right is what I'd like the final product to look like. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you SO much for your help!
<o></o>
http://www.4shared.com/file/uofupWe7/Day_By_Day_Investment_Tracker_.html

-Lars

I tested this one on the file you sent:

Code:

Sub laleph()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step -1

    If Range("A" & i) >= Date Then
    
        Range("C" & i).Value = Range("B" & i).Value
    
        Range("C" & i).Value = Range("C" & i).Value
        
    End If
    
Next i




End Sub

I think it works, unless I'm missing something. I assumed that the value in Column C is what you wanted too keep since Column B will change daily. One question - Why are there "FALSE"s in Column C from the current date down. Is there something else going on (ie a formula or something)?

laleph · May 3, 2011

Exactly! Column B will change daily, and I want to keep the value in column C. There are "FALSE"s in Column B because I had column B previously setup with a VLOOKUP saying, "IF Column A (Date) = Date of imported Data (Sheet 2 B3), THEN return value of portfolio, otherwise return "FALSE""

Unfortunately that code didn't seem to work for me. When I ran the Macro litterally nothing happened.

Is there perhaps a way to write a macro that detects changes on sheet2. If sheet2 changes. The macro will update the appropriate line automatically on sheet1. This way the value would be a real value and not a formula that would change when the data changes on sheet2?

Again thank you SO much for your help. If I can somehow repay you once this bad-boy ends up working PLEASE let me know!

-Lars

IF x THEN Paste Value?

laleph

New Member

Excel Facts

John Davis

Well-known Member

laleph

New Member

Mefu

New Member

laleph

New Member

John Davis

Well-known Member

laleph

New Member

Mefu

New Member

John Davis

Well-known Member

laleph

New Member

Similar threads

Forum statistics

Share this page

IF x THEN Paste Value?

New Member

Excel Facts

Well-known Member

New Member

New Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock