Macros for Consolidating Projects

After creating the template file, you can create separate models for a lot of different projects which will become part of a consolidated portfolio.  The general trick is to make sure that all of the inputs for the model are driven by Each of the projects is defined by a name that becomes the sheet name.  You can then run or create a macro to copy the template sheet to another sheet. Once you have a lot of templates, you can consolidate the templates into a consolidation.  The consolidation uses the INDIRECT function to gather data from the different files. A few screenshots may give you an idea of how the file to consolidate different SPV’s works.  The first picture is intended to give you an idea of how the final consolidated process works.  This consolidation works by using the INDIRECT function to add together cash flows and balances from individual sheets. The second screenshot below shows how you can use the INDIRECT function. The INDIRECT function looks painful, but you can get the hang of it quickly.




Macro Examples to Consolidate Files

Some examples of macros that copy and paste the template are shown below. Besides defining the range names, you should be able to copy these macros into your sheet. You can also look at them and then try to write the macros by yourself.


Sub CreateTemplate()

lastsheet = Sheets.Count ' This is the number of the sheets to move the file to
currentsheet = ActiveSheet.Name
 ' This is where to go back to after finished

Newspv = row 
' This is a public variable from the multiple sheet row

Sheets("Master").Select ' Start with the TEMPLATE

Newname = Cells(Newspv, 2) 

' This is the PROJECT NAME that you put in the template sheet to define variables

Sheets("Template").Select ' Go to the template sheet
Sheets("Template").Copy After:=Sheets(lastsheet) 

' Copy the sheet to the end of the file

Sheets(lastsheet + 1).Select ' Go to the new sheet that you made
Sheets(lastsheet + 1).Name = Newname ' Put the name of the new sheet in the file
Cells(1, 2) = Newname

Sheets(currentsheet).Select ' Go back to the original sheet

End Sub


The next macro shows how you can consolidate differnt files. The above macro is only for one sheet.

Sub multiplesheet()

remember_calc = Application.Calculation ' Will set calc to manual, so remember last value
Application.Calculation = xlCalculationManual ' Set to manual

Dim startrow, endrow As Single ' Need to dimension the variables that are defined by the input box

    startrow = InputBox("Ligne de début ?")   
    endrow = InputBox("Ligne de fin ?")

For row = startrow To endrow ' Loop around rows in the master page
        CreateTemplate ' Run the macro that creates a template

Next row

Application.Calculation = remember_calc ' re-set calculation to original value

End Sub


Macro with Screens that Show Which SPV is Being Created

There is a lot of psychology in financial models surrounding the time it takes to complete a task. A few seconds of waiting for something to recalculate can seem like forever. In the code below I use two user forms that allows you to see the project being created without having to press a button. The first thing is to create userforms. To create the userform just go to VBA menu and insert as shown below. You should create two userforms and not one. This will allow you to see what is being created without stopping. You will have to use the MOD function.

After inserting the userform, you can add labels and or text boxes and other items. You can put in pictures in the Userform and have the userform tell you what SPV you are created. You can do the same thing when you delete the files. The manner in which the userforms are shown is illustrated in the screenshot below.

The coding to allow this method is shown below. Note that the first thing to do is to use the userform1.label1.value = ” Project to change “. This will allow different items to be shown in the userform. You could add values to this or you could put in the time and you could use the FORMAT function in VBA which is similar to the TEXT function in excel. After you put in what you want in the userform, you need to include the statement DoEvents.


Then, to print the forms, you can use the MOD function in excel. This is important and will allow you to show one form, then remove the form and show the next form. Once you use the MOD function to show one of the two forms, you use the following code to show the userforms. Finally, at the end of the process, you should remove all of the userforms that you have made.

UserForm3.Show vbModeless
Unload UserForm4

Sub Create_Projects()
' Create_Projects Macro
current_status = Application.Calculation
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

start_sheet = Sheets("Start").Index

MsgBox " Copying Template to Projects After Sheet " & start_sheet & " Total Projects " & Range("num_projects")
Range("time_start") = Time

Count = 1

For sheet_num = 1 To Range("num_projects").Value
    If Range("project_select").Cells(1, sheet_num) = True Then
        Count = Count + 1
        UserForm3.Label2 = " Project " & sheet_num
        UserForm4.Label2 = " Project " & sheet_num
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
'    Application.Wait waitTime
        DoEvents  ' This is necessary for displaying forms
        start_sheet = start_sheet + 1
        Range("project_name") = Range("projects").Cells(1, sheet_num)
        Application.Goto Reference:="copy_range"
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveWindow.DisplayGridlines = False
        On Error GoTo error1:
        ActiveSheet.Name = Range("project_name")
'        Application.StatusBar = " Copying Project " & sheet_num
'     MsgBox sheet_num Mod 2
     If Count Mod 2 = 0 Then                   ' Go between the two forms
        UserForm3.Show vbModeless
        Unload UserForm4
        UserForm4.Show vbModeless
        Unload UserForm3
     End If
    End If

Next sheet_num

        Unload UserForm4
        Unload UserForm3

Application.Calculation = current_status
' Application.StatusBar = False

        Range("project_name") = Range("projects").Cells(1, 1)

Range("time_end") = Time

Application.ScreenUpdating = True


GoTo final_end:


MsgBox " Please make sure the sheets are deleted "


End Sub

Playlist on Creating and Anlysing Portfolio of Projects or Assets

I have put together some of the videos that I have made on creating a portfolio of projects or SPV’s. Some of the videos do not reflect the final method that I have created and may be repetitive. But if you want to torture yourself you can take a look. The various videos in this playlist include different examples of how to create a portfolio and write the macros.