Stylus Tip: Save Time Generating Reports with Excel and Automation

mpi Stylus Pro has the ability to eliminate the redundant process of manually creating and running reports.  This is accomplished through the use of automation and, specifically, FOREACHINXLS.  With this command and an Excel worksheet you can increase the speed of creating and exporting multiple reports and free up your time for other tasks by automating a manual process.  You can easily apply this command to any template your firm uses within Stylus.

Described below is the process used to run three reports based on the managers in the Excel sheet attached.  It does this by adding the inputs we have specified in the Excel file.

How to use it

In order for this example to work correctly just follow the directions below:

1. Save Sample_Excel_Automation.sty in the folder C:\Stylus\study\sample\

 * (Google Chrome users…to save this file please right click and click “Save link as”)

2. Save Excel_Automation_Example.xls in the folder C:\Temp\

3. Open the Sample_Excel_Automation.sty in mpi Stylus

4. Click “Run Script”, located in the script tab of the Study node.

Excel sheet

Stylus reads this Excel sheet to see what inputs to include in the report.  There are four sections of inputs; Fund, Benchmark, Peer Group and Style Outline.  You will notice that each section here requires two IDs; database ID (DBID) and a file ID (ID). Your Stylus script reads through the Excel sheet on a row by row basis and adds the inputs to the Stylus report.




Stylus Study

The FOREACHINXLS command can be viewed in the Stylus study.  To view this, go to the Study node in the designer and then click on the Script tab that appears on the pane on the right hand side (see the screen shot below).











To see an explanation of what commands and statements this script requires go to the Automation Reference provided within the Stylus application by going to Help -> Automation Reference.

The first statement defines the location of the excel sheet along with the worksheet name and cells being referenced.



Next, we’ll clear all of the inputs to make sure the study does not have anything already added that we don’t want to include.





After clearing all of our input nodes we’ll add all of our inputs so that the study runs properly.





The last statement defines which report in the study to export, where to export it and the name to give it (in this case we name it @COLUMN_2{} which is replaced by the ticker in the Excel worksheet).




The way this script is setup it will execute each command between FOREACHINXLS and ENDFOREACHINXLS for every row we defined in the cell range (in this case A3:J200).  For instance it will look at the first row in the Excel: clear all inputs, add all inputs, run the study, export the study, then look back to Excel and repeat this process.

For questions regarding this script please contact our support team.

*Please note a subscription to our Automation Package within Stylus Pro is required.



No comments yet... Be the first to leave a reply!

Leave a Reply