portfolio utility installation & user guide · portfolio utility installation & user guide...

Portfolio Utility Installation & User GuideContentsOverview 2

Installation at a Glance 2

Installation Requirements 3

Machine Requirements 3

Network Ports & Firewalls 3

Panaray Entitlements 3

Advent APX Configuration 4

Installing the Java Runtime Environment (JRE) 4

Installation & Configuration Procedure 5

Installation 5

Script File Configuration 6

Running the Utility 8

Command Line Options 10

Scheduled Uploads 12

Appendix 14

Appendix A: Portfolio File Format 14

© 2012 William O’Neil + Co. All rights reserved. ‘PANARAY’ and the PANARAY logo are registered trademarks and trademarks of William O’Neil + Co.

Document Version 1.0.2

®

PORTFOLIO UTILITY INSTALLATION & USER GUIDE 2®

OverviewThis guide describes the installation and usage of the PANARAY Portfolio Utility. The utility is used to upload portfolio holdings data into PANARAY from text files or an external portfolio management system such as Advent APX.

The utility can be run from the command line or scheduled using your existing production scheduler (e.g. Windows Task Scheduler, SQL Server Scheduler, etc.). The release package consists of Java JAR files, a set of script files to run the utility, and an example portfolio text file to test the installation.

The utility is limited to uploading holdings data (i.e. a list of securities and the number shares held). This release does not support the import of transaction or tax-lot data.

Portfolios uploaded by the utility are stored in a secure PANARAY database that is separate from other user lists. Users access their portfolios in PANARAY via the Portfolio group in List Manager.

Installation at a GlanceThe installation procedure consists of these main steps which are described in more detail in the Installation & Configuration section of this guide.

• STEP 1: Verify Installation Requirements• STEP 2: Install PANARAY Portfolio Utility package• STEP 3: Configure APX Data Source (required only for APX installs)• STEP 4: Test Installation and Configuration• STEP 5: Schedule Reoccurring Uploads (optional)


Installation RequirementsThis section describes the system pre-requisites needed to install and run the PANARAY Portfolio Utility.

Machine Requirements

Operating System

The utility runs on Microsoft Windows platforms. The utility has been tested to run on the following versions of Windows.

9 XP – Service Pack 3

9 Windows Vista

9 Windows 7

Available Disk Space The installation requires less than 15Mb of disk space to install.

Network Connection 2 Mbps network connection and <200 ms latency when pinging services.panaray.com

Software

9 Java Runtime Environment (JRE) – See Installing the JRE section below.

9 JDBC Database Driver – Required for uploads from Advent APX only. See instructions in the Installation & Configuration section.

Network Ports & FirewallsThe utility sends/receives data over the intranet via SSL using TCP 8443. The machine where the utility is run must have internet access via this port.

Personal firewall and filter programs such as the Windows® firewall, Norton™ Firewall (Internet Security), ZoneAlarm®, and Ad-Aware® may block access to the Internet. To determine if your access is blocked by a personal firewall, try running the utility with the firewall disabled.

Please consult the help files or the manufacturer’s manual for your personal firewall software for instructions on adding the PANARAY Portfolio Utility to the firewall’s exception list.

PANARAY EntitlementsThe portfolio utility requires that at least one PANARAY user at your firm have the “Portfolio Administrator” entitlement. The Portfolio Admin user account specified in the configuration file must have this entitlement.

In addition, each user that will view portfolios in PANARAY must have the “Portfolio User” entitlement.

To setup these entitlements for your firm’s PANARAY accounts, please contact Institutional Client Services at [email protected] or call 800.419.6632.

services.panaray.com


Advent APX ConfigurationIf you import portfolios from Advent APX, the mapping between portfolios in APX and the PANARAY user that sees that portfolio is accomplished by setting the portfolio’s URL attribute to the PANARAY user’s email address.

For example, if you wanted to allow a portfolio manager to see a particular portfolio in PANARAY you would edit the portfolio’s URL attribute in APX to include the PANARAY user id (typically the user’s email address) for the portfolio manager that will see the portfolio in PANARAY.

In the example above, the portfolio will be mapped to the PANARAY user [email protected].

Installing the Java Runtime Environment (JRE)The Java Runtime Environment (JRE) must be installed on the PC where the utility will run. To check if the command line Java tool is installed type “java” into a Windows command line prompt. If you see the Java usage statement, then the JRE is installed. If “java” is not found, then you must install the JRE.

The JRE is freely available from Oracle at the URL below. http://www.oracle.com/technetwork/java/javase/downloads/index.html

http://www.oracle.com/technetwork/java/javase/downloads/index.html


Installation & Configuration ProcedureThis section describes the steps to install the PANARAY Portfolio Utility on a client machine.

Installation

To install the PANARAY Portfolio Utility:

1. Download PANARAY Portfolio Utility – Download the PANARAY Portfolio Utility installation package from panaray.com: http://www.panaray.com/downloads

2. Extract the Archive File – Extract the portfolio utility archive file into the directory where you want to install the utility. Expanding the archive will create a “portfolio-client” directory.

3. Confirm Installation – The installation process extracts the utility files into the installation directory. It creates the directories / files listed below:

4. APX INSTALLS ONLY: Install the JDBC Database Driver – If you will be uploading portfolios from Advent APX, you will need to download, install, and configure a Java Database Connectivity (JDBC) Driver. The portfolio utility needs this driver to connect to your Advent APX database in order to download portfolio data. Since Advent APX can be configured to use various databases, you will need to determine the right driver for your installation. Several popular database vendors support JDBC drivers and these drivers are freely available.

The download URLs for Microsoft SQL Server and Oracle database are listed below. NOTE: These URLs are subject to change by the provider and are provided below for reference only.

9 Microsoft JDBC Driver for SQL Server: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

9 Oracle JDBC Driver for Oracle Database: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

Once the database driver is installed, you will need to configure the database environment variables in the portfolios-apx.bat script to use the driver that you installed in this step. You will need to know the directory where the driver JAR files are installed.

If you have trouble installing the utility, please contact Institutional Client Services at (800) 419-6632 or [email protected].

Portfolio-Client This is the top level installation directory.Bin A directory will the scripts file that run the upload.

portfolio-file.bat This is a script file that contains the commands to import a portfolio from a tab separated text file into PANARAY. portfolios-apx.bat This is a script file that contains the commands to export portfolios from Advent APX and import them into PANARAY. sample-portfolio.txt This is a sample portfolio that can be used to test the installation.

Lib A library directory with the Java code that perform the upload.7 JAR Files These files contain code libraries that perform the import


Script File ConfigurationTo configure the PANARAY Portfolio Utility you will need to edit the Windows batch files that are located in the “portfolio-client/bin” installation directory. The batch file scripts contain the commands to set the required environment variables and run the portfolio import. These scripts are distributed in template form and should be edited to suite your specific environment.

Text File Script Configuration (portfolio-file.bat)If you plan to import portfolios from text files, you may need to edit the portfolio-file.bat file that is located in the “portfolio-client/bin” directory. For most installations, however, you will not need to modify the default settings.

Environment Variable Settings – The variables listed below are set within the portfolio-file.bat script file. The information is used primarily for non-standard installations.

9 APP_HOME – This variable should point to the root-folder where the application is installed (un-zipped) to. Set this variable only if the batch script is executed remotely (e.g. from a shortcut located on your Windows desktop).

9 APP_OPTS – This variable is used to pass special Java options to the Java Virtual Machine such as setting JVM memory options.

9 JAVA_HOME – This is the folder where Java is installed. There is no need to set this variable as long as java.exe is in the system PATH.

Advent APX Script Configuration (portfolios-apx.bat)If you plan to import portfolios from Advent APX, you will need to edit the portfolios-apx.bat file that is located in the “portfolio-client/bin” directory.

Environment Variable Settings – The variables listed below are set within the portfolios-apx.bat script file. For most installations, you will only need to modify the JDBC environment variables (JDBC_CLASSPATH, JDBC_DRIVER_CLASS, and JDBC_URL) to suite your installation.

9 APP_HOME – This variable should point to the root-folder where the application is installed (un-zipped) to. Set this variable only if the batch script is executed remotely (e.g. from a shortcut located on your Windows desktop).

9 APP_OPTS – This variable is used to pass special Java options to the Java Virtual Machine such as setting the JVM memory options.

9 JAVA_HOME – This is the folder where Java is installed. There is no need to set this variable as long as java.exe is in the system PATH.

9 JDBC_CLASSPATH: This is the path to the jar file(s) containing the JDBC Driver. For example, if your APX installation uses Microsoft SQL Server and your Microsoft JDBC driver package was installed in the “C:\portfolio-client\“directory, then this variable would be set to: JDBC _ CLASSPATH= C:\portfolio-client\sqljdbc _ 4.0\enu\sqljdbc4.jar


9 JDBC_DRIVER_CLASS: This is the Java driver class that implements the JDBC driver. Please refer to the JDBC vendor documentation for help setting this variable. As an example, for the Microsoft JDBC Driver, this variable would be set to: JDBC _ DRIVER _ CLASS=com.microsoft.sqlserver.jdbc.SQLServerDriver

9 JDBC_URL: The connection URL to establish a connection to the Advent APX Public Views database. The correct setting for this variable is highly dependent on your specific Advent APX installation. As an example, if your APX installation uses Microsoft SQL Server and your Advent APX installation was on a SQL Server instance called “dbprod1\Advent”, and the database that contained the Advent APX installation was called “APXFirm”, and your database used the integrated security login model, then this variable would be set to: JDBC _ URL=jdbc:sqlserver://dbprod1\advent;databaseName=APXFirm;integratedSecurity=true;loginTimeout=1

If you have trouble configuring the utility, please contact Institutional Client Services at (800) 419-6632 or [email protected].


Running the UtilityOnce the utility is installed and configured you can run it by calling the utility script (i.e. portfolio-file.bat or portfolios-apx.bat) from the Windows command line or by creating a Windows batch script that calls the utility. Alternatively you can also schedule the utility script to run at regular times using a scheduler such as Windows Task Scheduler.

To run the utility from the Windows command line:

1. Open a Command Window - Open the Windows command prompt. The quickest way to do this is by typing “cmd” into the Start > Run or Start > Search prompt.

2. Change Directory - Change directory to the “bin” folder where the utility is installed. For example, if the utility is installed in “C:\portfolio-client\”, then type: cd C:\portfolio-client\bin

3. Run the Utility - From the “bin” directory, run the portfolio utility with the necessary command line options. You will run either “portfolio-file.bat” or “portdolio-apx.bat” depending on where your portfolios are located. The specific command line options for both utilities are different and are described in the next section. For example, to upload the sample portfolio text file into your PANARAY account, run the following command: portfolio-file.bat -authuser [PortAdminUser] -authpwd [portAdminPWD] -targetuser [PortUser] -file SamplePortfolio.txt -id test-1234 -name “Portfolio Test” UPLOAD where: [PortAdminUser] = PANARAY User Id of your Portfolio Admin account [PortAdminPWD] = Password for your Portfolio Admin Account [PortUser] = PANARAY User id of the user that will receive the portfolio upload. This can be any PANARAY user id that is associated with you company and can be the same as the Portfolio Admin account.

4. Check for Errors in Output – When the utility runs it sends messages to the console as it reads portfolios from the source and uploads them into PANARAY.

5. Open the Portfolio in PANARAY – Once the portfolio utility has completed, the uploaded portfolios will be available in the PANARAY user accounts that they were uploaded to. The portfolios appear within PANARAY List Manager under the “Portfolios” group. NOTE: Any open instances of PANARAY List Manager will need to be refreshed in order to see the newly uploaded portfolios. The easiest way to refresh List Manager is to open a new instance of list manager using the Configure gear ( ) in the upper right corner of the List Manager window.


Once you have successfully run the utility from the command line, you may want to create a simple script file that calls the utility with the correct options. This script could also be used to upload multiple portfolios at once.

To create a Windows batch script to run the utility:

1. Create a New Text File – Using a text editor such as Microsoft Notepad, create a new text file in the “bin” directory. You can name the text file anything, but it should have the “.bat” extension. NOTE: text editors will sometimes append “.txt” to the name of a text file. Be sure that the file you create ends in “.bat” or it will not be recognized as an executable by Windows.

2. Add the Following Lines – The commands that you add to the file will depend on your specific needs and where your portfolios are located. For example if you wanted to write a script to upload text portfolios for 3 portfolio managers: Steve, Alice, and Bob, your file might look like: UploadMyPortfolios.bat call portfolio-file.bat -authuser [PortAdminUser] -authpwd [portAdminPWD] -targetuser [Steve’s PANARAY User ID] -file StevePortfolio.txt -id steve-p1 -name “Steve’s Portfolio” UPLOAD call portfolio-file.bat -authuser [PortAdminUser] -authpwd [portAdminPWD] -targetuser [Alice’s PANARAY User ID] -file AlicePortfolio.txt -id alice-p1 -name “Alice’s Portfolio” UPLOAD call portfolio-file.bat -authuser [PortAdminUser] -authpwd [portAdminPWD] -targetuser [Bob’s PANARAY User ID] -file BobPortfolio.txt -id bob-p1 -name “Bob’s Portfolio” UPLOAD where: [PortAdminUser] = PANARAY User Id of your Portfolio Admin account [PortAdminPWD] = Password for your Portfolio Admin Account

3. Save the Text File – Save the file in the “bin” directory so that your new script will be in the same directory as the utility script files.

4. Run the Script File – From the “bin” directory, run the batch file that you created from the windows command prompt. No command line options are necessary. For example, to run the example above just type “UploadMyPortfolios.bat” at a command prompt.


Command Line OptionsAdvent APX (portfolios-apx.bat)

usage: portfolios-apx [options] [action type]

This utility uploads portfolios from an Advent APX installation into PANARAY. The mapping between Advent Portfolios and PANARAY users is accomplished by adding the PANARAY user’s email address to the Portfolio’s URL field in Advent APX. See the Advent APX Configuration section for more details. Using the DELETE action type, this utility can also be used to delete all portfolios that were previously uploaded by this utility.

Command Line Option Required? Description-authuser <login> REQUIRED PANARAY User ID of the Portfolio

Admin Account for your company. -authpwd <password> OPTIONAL Password for the PANARAY

Portfolio Admin Account listed above. If not specified then system will prompt to enter password at runtime.

-targetuser <user id> REQUIRED Default PANARAY User ID for which the portfolio will be created, if not user id is found within the Portfolio’s URL field.

-err <file> OPTIONAL The path and name of the file where errors will be logged. If not specified then errors are sent to std-err.

-out <file> OPTIONAL Log file where the output/informational messages are logged. If not specified then uses std-out.

-verbose OPTIONAL Prints additional information to std-out.

-help OPTIONAL Prints the usage help text to std-out.

[action type] REQUIRED Either 'UPLOAD' or 'DELETE'.

UPLOAD - Deletes all portfolios previously uploaded by this utility and then uploads new data from APX.

DELETE – Deletes all portfolios previously uploaded by this utility.


Text Files (portfolio-file.bat)usage: portfolio-file [options] [action type]

This utility uploads a portfolio from a tab separated text file into the specified PANARAY user account. This utility can also be used to delete a portfolio from an account.

-authuser <login> REQUIRED PANARAY User ID of the Portfolio Admin Account for your company.

-authpwd <password> OPTIONAL Password for the PANARAY Portfolio Admin Account listed above. If not specified then system will prompt to enter password at runtime.

-targetuser <user id> REQUIRED PANARAY User ID for which the portfolio will be created.

-err <file> OPTIONAL The path and name of the error log file to be created. If not specified then errors are sent to std-err.

-file <portfolio file> REQUIRED File name of the tab separated text file containing the portfolio to upload. This option is not required if action type is DELETE.

-id <portfolio id> REQUIRED The portfolio id for the uploaded or deleted portfolio within PANARAY. This should be a variable name with no spaces or special characters.

-name <portfolio name> REQUIRED The name of the portfolio as it will appear in the PANARAY List Manager. Use double quotes around portfolio names with spaces. This option is not required if action type is DELETE.

-verbose OPTIONAL Prints additional information to std-out. -help OPTIONAL Prints the usage help text to std-out.[action type] REQUIRED Either “UPLOAD” or “DELETE”.

UPLOAD - Uploads the portfolio identified by -id option with new data from file.

DELETE - Deletes the portfolio identified by -id option.


Scheduled UploadsIf you want to upload your portfolios into PANARAY on a regular basis, for example after you receive a daily update from a custodian brokerage, the easiest way to do this is to schedule a reoccurring job using a production scheduler such as Windows Task Scheduler, SQL Server Scheduler, or a third party scheduler.

Although it is possible to setup this job on a desktop PC, the scheduled job would typically be setup on a server that is part of you firm’s production environment. You will need to ensure that the computer on which the job runs has the appropriate connectivity to the Intranet and (for Advent portfolio uploads only) a connection to the database via a JDBC driver. See the Installation Requirements section of this document for more information about the prerequisites to run the utility.

To schedule the portfolio upload via Windows Task Scheduler do the following:

1. Install and Configure the Portfolio Utility – You’ll need to install and configure the utility on the PC where the job will run or on a shared drive that the machine can access. See the Installation and Configuration Procedure for details on how to do this.

2. Create a Batch Script to Run the Utility - Once you have successfully installed and configured the utility, you may want to create a simple script file that calls the utility with the correct options. This script file will make scheduling and maintaining the job easier. See the Running the Utility section of this guide for how to create this script file.

3. Open Windows Task Scheduler – On the computer where you want the job to run, open Windows Task Scheduler. The easiest way to do this is to search for “Task Scheduler” using the Start > Search command and click on the “Task Scheduler” search result.

4. Create a New Job – You’ll need to create a new job to run the utility. In Windows Task Scheduler select Action > Create Basic Job from the menu.

5. Enter the Job Name and Triggers – The next set of screens will guide you through the process of setting up the job name, frequency, and schedule time. Fill in these fields as needed.


6. Select the Job Action – The job action should be setup to run the portfolio utility script file that you created in step #2. NOTE: Be sure to set the “Start in” option to the directory where the script file is located (e.g. “C:\portfolio-client\bin\”).

7. Finish the Job – Click the Finish button to save your job.

8. Test The Job – To test the job, you can select it from the list of jobs and click the “run” action. The job will run and you’ll see a command line window popup with the output of the upload.


APPENDIX

Appendix A: Portfolio File Format The utility can import portfolio holdings from tab delimited text files. This section describes the format of the text files. The format of the file is described below.

[security identifier]"[identifier type]"[number of shares held] 8[security identifier]"[identifier type]"[number of shares held] 8[security identifier]"[identifier type]"[number of shares held] 8

• [security identifier] = The security identifier for the row. See supported types below. Required. • [security identifier type] = A numeric code that identifies the type of security identifier for the row in the

text file. This field is required. The possible values are: y 0 = William O’Neil + Co. Symbol - The William O’Neil + Co. symbol. (e.g. AAPL, [email protected]) y 1 = CUSIP - The identifier assigned by the CUSIP committee. (e.g. G6075410, 55002110) y 3 = SEDOL - The identifier assigned by the London Stock Exchange. (e.g. 6900643, 6158163) y 4 = Bloomberg Symbol - The Bloomberg symbol displayed in PANARAY. (e.g. BA/ LN, 5930 KS)

• [number of shares held] = The number of shares held for the security. This value can be positive to indicate a long position or negative to indicate a short position. This field is optional.

• " = tab character• 8 = end of line character

ExampleThe example below shows a sample portfolio file with 8 positions:

SamplePortfolio.txt

AAPL 0 100 100 shares of Apple Inc. (William O’Neil + Co. symbol)[email protected] 0 100 100 shares of Mitsubishi on Tokyo Exchange (William O’Neil + Co. symbol)BA/ LN 4 100 100 shares of BAE Systems PLC on the London Exchange (Bloomberg symbol)5930 KS 4 100 100 shares of Samsung Electronics on KOSDAQ Exchange (Bloomberg symbol)G6075410 1 100 100 shares of Michael Kors Hldgs Ltd (CUSIP)55002110 1 100 100 shares of Lululemon Athletica (CUSIP)6900643 3 -100 100 short shares of Toyota on the Tokyo Exchange (SEDOL)6158163 3 100 100 shares of HSBC Holdings on the Hong Kong Exchange (SEDOL)

Notes • Column Headers - Column headers are not supported and should not be included in the file.• Symbol Resolution – In the cases where the SEDOL matches multiple securities, the security with the highest

average daily dollar volume will be used. • Cash Positions – Cash and cash equivalents are not currently supported.

portfolio utility installation & user guide · portfolio utility installation & user guide...

Documents