Creating a Portfolio Spreadsheet using the MIM Excel Add-In
This document outlines how to run a query using the MIM Excel Add-In, pull the MIM’s output from one spreadsheet and organize it into another, and automate the final worksheet for easy use. The workbook “Bear.xls” shows the historical impact on a portfolio of stocks following a 5% drop in the Nasdaq and a 2% drop in the S&P500. The reader is assumed to have a basic understanding of the MIM Excel Add-In, MIM query language, and Visual Basic programming for Excel.
Open the Bear.xls workbook. The first worksheet that displays is “BearMove”. This is the worksheet where the MIM query’s output will display. To see the query, click on “MIM” in the Excel toolbar and choose “MIM Queries”. The Query List displays the query entry “BearMove”. Double click on the highlighted “BearMove” entry to display the “Edit Query” screen containing the following “BearMove” query.
#Shows % moves up to 5 days later after the WHEN condition occurs#
LET
theSec = CSCO, DELL, INTC, MER, YHOO
SHOW
theSec: theSec
t+1: % move from today to 1 day later of theSec
t+2: % move from today to 2 days later of theSec
t+3: % move from today to 3 days later of theSec
t+4: % move from today to 4 days later of theSec
t+5: % move from today to 5 day later of theSec
WHEN
Date is after 10/26/1997
AND
{ NASD is down more than 5 %
AND
SPX is down more than 2 % }
The above query language is asking the MIM to shows us what happens 1 day, 2 days, …5 days later for each of the 5 equities when the Nasdaq is down more than 5% and the S&P500 is down more than 2% on the same day. For more information on the query language refer to the XMIM User Guide located at www.lim.com.
Select Run, and close the Edit Query screen. The sheet “BearMove” is now populated with the results from our query. You will notice that since 1997, our WHEN statement has happened 15 times and the MIM has returned the percent moves for each of the 5 equities. The worksheet BearMove now matches the output in the XMIM’s Answer screen if you were to run this same query in XMIM. FYI, the date 10/26/1997 was added to ensure that all 5 equities were actively traded in order to get accurate results.
Now that we have the BearMove worksheet populated with the raw data from the MIM, select the second worksheet labeled “Portfolio”. Select the button on the spreadsheet named “Portfolio”.
When you select the Portfolio button, the Portfolio worksheet pulls some of the data from the BearMove worksheet and populates Columns A thru G using Visual Basic code. Column A shows the 5 equities, Column B shows the closing price on the last (most recent) occurrence date, and Columns C thru G show the averages of the 1-5 day percent moves for each equity. This information is located on the BearMove worksheet and is simply rearranged in a format that is easier to read and manipulate in the Portfolio worksheet.
We want to use these average percent moves to calculate the weighted moves and then the total change in our 5 stock portfolio entries. Column H shows that each equity is equally weighted in our portfolio (.2 or 20%) and Column I returns the product of the weight and the 5 day percent move for each stock (Column G). Sum up the weighted moves to arrive at our total percent move. To reiterate, that is the total of the weighted 5 day percent moves of our equally weighted 5 equity portfolio entries WHEN, historically, the Nasdaq is down 5% and the S&P is down 2% in the same day. Basically, we are looking at what our portfolio will do 5 days after our WHEN condition.
To see the code used to create the Portfolio worksheet, hold down Alt key and select the F11 key. As you can see, this operation is pretty easy to accomplish just by using a few loops and a couple of variables. Next let’s see how to automate the process.
The Portfolio button located on the Portfolio worksheet is separate from the “portfolioMacro” and contains several other functions that makes it easy to re-run and update the data just by selecting the button. To see the code for the button, select “View” from the toolbar then Toolbars and Control Toolbox. A small strip of buttons should appear on your screen. Click on the Design Mode button and then click on the Portfolio button located on the Portfolio worksheet. Next, select “View Code” from the Control Toolbox and Visual Basic will pop back up with the button’s code. Every time you click the Portfolio button, this small bit of code tells Excel to first clear the entire sheet, refresh the data from the MIM server and then run the portfolioMacro. The following is the code for the button macro:
Private Sub CommandButton1_Click()
'Clears the sheet
Cells.Select
Selection.clear
Range("A1").Select
Application.Run "MIMExcel1_1.xla!RefreshData"
'Run "portfolio" Macro
Application.ScreenUpdating = False
portfolio
Application.ScreenUpdating = True
End Sub
The first section of code simply clears the Portfolio worksheet every time you click the button so that you are not overwriting previous data.
The line:
Application.Run "MIMExcel1_1.xla!RefreshData"
tells the MIM Excel Add-In to refresh the data for all queries and data from the MIM database. This will ensure that each time portfolioMacro runs, the query will pull the most recent data from the database. The two “ScreenUpdating” lines that enclose portfolioMacro help to keep the Excel spreadsheet from flashing each time you run the macro.
Open the Control Toolbox from View>Toolbars>Control Toolbox and click “Design Mode”. Then select the Command Button from Control Toolbox and use the crosshairs to size your box on the spreadsheet. Then, click on View Code and a VB module will appear with the code for the button. You can now insert other macros or functions you would like your button to perform. When you are finished, click off Design Mode and save your code.
While viewing the code for your button in VB, go the Properties window located under View in the VB toolbar. Under the title “Caption”, click on the phrase CommandButton1 and change it to something else.
You will get the message box because the button is coded to refresh the MIM data every time you click it, but you have no data in the Add Data section of the Add-In to refresh. Open a new worksheet in your Workbook (Insert>Worksheet). Go to MIM>Add Data. Choose something simple and small such as Close of DELL for 1 week, select Finish and then select MIM>Refresh Data. Save your workbook. Now when you hit your button the Message Box will not display.
You can make the dummy worksheet invisible in the Visual Basic editor. Select the Alt + F11 keys to go to the Visual Basic mode. Select the worksheet you want to hide in the upper Project window. In the bottom Properties window, select “Visible” then use the pull down arrows to change the entry to “Hidden”.