Copy daily portfolio value into a new sheet

[pyclen]

Hi everybody

I made a spreadsheet with a mutual fund portfolio, and I am interested in saving the daily value changes
I copied below an example of what I mean (not real number of holdings/shares), there are 2 funds and their respective data (is pulled automatically as both funds are formatted as stock), how many shares I own (50), the current values, and some other info about the funds.

What I want is to have the total of the two funds (here 11269.5) copied into a separate sheet with the date, resulting in a pair of values so one can make a graph.
I have Office 365


Thank you

Shares	value	Change		1yr	3yr	5yr	YTD
Fidelity Contrafund	50	1081	$ 0.10	$ 21.62	44.65%	11.44%	19.25%	33.84%
Fidelity 500 Index Fund	50	10188.5	$ 0.81	$ 203.77	37.23%	10.89%	16.18%	23.81%
11269.5

 

[Kaper]

The easy way to achieve this would be by using VBA code. To free you from remembering about running it, I'd do this as event handler for workbook_open event.
The tasks for this macro would be:
1) Check the date - if it is the same as previously recorded - exit
2) Copy the value to cell with last stored date (there was a formula - see step 4)
3) Write today's date below last stored date in "archive" sheet
4) Write a formula next to date referring to a cell with current sum (until data is changed it will be the same as value above)

Having a formula would be a help for a graph including today's data.
I'd also name a cell containing total value using name manager -> select this cell, press Ctrl+F3 - >New and name it Total
Make sure you have at least one row of old data in Archive worksheet.

Now the code:

Private Sub Workbook_Open()
Dim lr As Long
With Sheets("Archive")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  If .Cells(lr, "A") <> Date Then
    .Cells(lr + 1, "A") = Date
    .Cells(lr, "B") = Worksheets("Portfolio").Range("Total").Value
    .Cells(lr + 1, "B").Formula = "=Total"
  End If
End With
End Sub

It have to be placed in Thisworkbook module. You can open VBA editor with Alt+F11 or if this shortcut is taken by Nvidia (my case ) you can rightclick on a sheet tab and select View Code
then doubleclick on Thisworkbook in the project Tree on the left and paste the above code. Close VBA editor and save your file as macro-enabled (xlsm). Probably on next opening you will have to mark this file as trusted one.

Close the file and reopen it. if the last date in column A (change columns in code if needed) is not current day the code will make steps 2-4 expanding your Archieve table one row.
BTW. I'd change the table in Archive sheet into excel table (insert-> table) to be "double sure"* that ranges used in graph do change automatically. (if there is already a graph delete it, create a table and recreate graph based on table. *) Your version shall do this properly and autoexpand data ranges for graph also without table.

 

[pyclen]

The easy way to achieve this would be by using VBA code. To free you from remembering about running it, I'd do this as event handler for workbook_open event.
The tasks for this macro would be:
1) Check the date - if it is the same as previously recorded - exit
2) Copy the value to cell with last stored date (there was a formula - see step 4)
3) Write today's date below last stored date in "archive" sheet
4) Write a formula next to date referring to a cell with current sum (until data is changed it will be the same as value above)

Having a formula would be a help for a graph including today's data.
I'd also name a cell containing total value using name manager -> select this cell, press Ctrl+F3 - >New and name it Total
Make sure you have at least one row of old data in Archive worksheet.

Now the code:

Private Sub Workbook_Open()
Dim lr As Long
With Sheets("Archive")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  If .Cells(lr, "A") <> Date Then
    .Cells(lr + 1, "A") = Date
    .Cells(lr, "B") = Worksheets("Portfolio").Range("Total").Value
    .Cells(lr + 1, "B").Formula = "=Total"
  End If
End With
End Sub

It have to be placed in Thisworkbook module. You can open VBA editor with Alt+F11 or if this shortcut is taken by Nvidia (my case ) you can rightclick on a sheet tab and select View Code
then doubleclick on Thisworkbook in the project Tree on the left and paste the above code. Close VBA editor and save your file as macro-enabled (xlsm). Probably on next opening you will have to mark this file as trusted one.

Close the file and reopen it. if the last date in column A (change columns in code if needed) is not current day the code will make steps 2-4 expanding your Archieve table one row.
BTW. I'd change the table in Archive sheet into excel table (insert-> table) to be "double sure"* that ranges used in graph do change automatically. (if there is already a graph delete it, create a table and recreate graph based on table. *) Your version shall do this properly and autoexpand data ranges for graph also without table.

Thanks Kaper for your reply, however this is not working in any way for me
I have data starting in B1 through L1 (this is just the example)
The total would be in e4 here, and the code just copied the date, nothing else

not sure what is going on

 

[Kaper]

So the data is in a bit different layout (moved right) and as on screenshot - E4 is empty, so there is no total cell. No problem.
Try such ammended code:

Private Sub Workbook_Open()
Dim lr As Long
With Sheets("Archive")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  If .Cells(lr, "A") <> Date Then
    .Cells(lr + 1, "A") = Date
    .Cells(lr + 1, "B").Formula = "=SUM(Portfolio!E:E)"
    .Cells(lr, "B").value = .Cells(lr+1, "B").value
  End If
End With
End Sub

Remember to insert it in thisworkbook module (which you probably did before, as you got - unfortunately - date only, but it worked at least a bit). And remember to change last date in archive sheet to yesterday or some day in the past

 

[pyclen]

Thank you for the updated code, and yes, that worked

How would I do that if I have 3 different portfolios in the sheet like the example below

 

[pyclen]

nevermind, I changed the formula to a what I need, now it calculates correct

thanks again for your help

 

[Kaper]

Glad to hear so. You may mark my previous post as a solution Mark as Solution

 

[pyclen]

Glad to hear so. You may mark my previous post as a solution Mark as Solution

Did that
one other question

how do I change the code if I want to monitor two separate portfolios (like mine and another one) that are both in the same XL file (different tabs though)

 

[Kaper]

assuming second worksheet name is Portfolio2 and to be archived in column C add just 2 lines similar as for Portfolio worksheet

Private Sub Workbook_Open()
Dim lr As Long
With Sheets("Archive")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  If .Cells(lr, "A") <> Date Then
    .Cells(lr + 1, "A") = Date
    .Cells(lr + 1, "B").Formula = "=SUM(Portfolio!E:E)"
    .Cells(lr, "B").value = .Cells(lr+1, "B").value
    .Cells(lr + 1, "C").Formula = "=SUM(Portfolio2!E:E)"
    .Cells(lr, "C").value = .Cells(lr+1, "C").value
  End If
End With
End Sub

 

[pyclen]

assuming second worksheet name is Portfolio2 and to be archived in column C add just 2 lines similar as for Portfolio worksheet

Private Sub Workbook_Open()
Dim lr As Long
With Sheets("Archive")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  If .Cells(lr, "A") <> Date Then
    .Cells(lr + 1, "A") = Date
    .Cells(lr + 1, "B").Formula = "=SUM(Portfolio!E:E)"
    .Cells(lr, "B").value = .Cells(lr+1, "B").value
    .Cells(lr + 1, "C").Formula = "=SUM(Portfolio2!E:E)"
    .Cells(lr, "C").value = .Cells(lr+1, "C").value
  End If
End With
End Sub

Thank you, appreciate it