Hi Experts,
I have written a VBA macro to compute returns of my different investment portfolio, which works perfectly fine and serves my purpose pretty well. But my VBA skills are at a very very amatuer level. Seeking your expertise and help to shorten & concise this macro. It should be good learning as well help for me. Thanks in advance.
Here is the macro written:
I have written a VBA macro to compute returns of my different investment portfolio, which works perfectly fine and serves my purpose pretty well. But my VBA skills are at a very very amatuer level. Seeking your expertise and help to shorten & concise this macro. It should be good learning as well help for me. Thanks in advance.
Here is the macro written:
Code:
'Macro for computing Individual Portfolio returns
''Select and copy the data to be used
Sheets("Holding Details").Select
Range("F:F,G:G,I:I").Select
Selection.Copy
''Paste the data selected
Sheets("XIRR Calculator").Select
Range("M1").Select
ActiveSheet.Paste
''Multiply the values to be read as outflow of money
Range("D1").Select
ActiveCell.FormulaR1C1 = "-1"
ActiveCell.Copy
Range("O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
''Capture todays date
Range("N" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("N" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("N" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("N" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("N" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("N" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
''Capture current value of investement
Range("O" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Sheets("Returns Summary Sheet").Range("D2")
Range("O" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Sheets("Returns Summary Sheet").Range("D3")
Range("O" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Sheets("Returns Summary Sheet").Range("D4")
Range("O" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Sheets("Returns Summary Sheet").Range("D5")
Range("O" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Sheets("Returns Summary Sheet").Range("D6")
Range("O" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Sheets("Returns Summary Sheet").Range("D9")
''Capture Equity and Debt labels against current value of investement
Range("M" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Beginner"
Range("M" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Safe player"
Range("M" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Ultracool"
Range("M" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Adventurer"
Range("M" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Others"
Range("M" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Fixed guns"
''Apply Filter on the range selected
Range("M1:O1").Select
Selection.AutoFilter
''Sort the data according to date
ActiveWorkbook.Worksheets("XIRR Calculator").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("XIRR Calculator").AutoFilter.Sort.SortFields.Add Key _
:=Range("N1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("XIRR Calculator").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
''Sort the data according to portfolio
ActiveWorkbook.Worksheets("XIRR Calculator").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("XIRR Calculator").AutoFilter.Sort.SortFields.Add Key _
:=Range("M1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("XIRR Calculator").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
''Clear Auto Filter
ActiveSheet.AutoFilterMode = False
''Compute XIRR of the investment
Range("P2").Select
ActiveCell.FormulaArray = "=IF(RC[-3]=R[-1]C[-3],"""",XIRR(IF(RC[-3]:R[4997]C[-3]=RC[-3],RC[-1]:R[4997]C[-1],0),RC[-2]:R[4997]C[-2]))"
Range("O" & Rows.Count).End(xlUp).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "A"
Range("P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Last edited: