Addenda

Item changed: "Keep Extra Column for GST"

If installed new, the setup dialog (figure 3.1.1) will be as shown, querying whether you want to record the GST in a separate column (ie forgoing an extra "Sundry Fees" per transaction).

If a prior version is updated, the program will no longer pass through the setup dialog; therefore, a special question will be asked: "Do you want to…?" Reply as appropriate, and any existing data will be adjusted as required. This adjustment will only apply to existing portfolios that had data recorded with GST included in Brokerage. That component will be separated out and transferred into its own column –but only if the transaction date lies after the date when the GST was introduced.

Function changed: Acquisition and Disposal Panels

On both panels, a "Cash Total" has been added in the bottom right-hand corner. This amount will be updated every time one of the cash elements changes: net amount, brokerage, stamp duty, and sundry. In case of acquisitions, it is the sum of all components; in case of a sale, it is the proceeds minus costs.

Function changed: Profit/Loss in Trades Charts

The charts now distinguish between trading profit (green) and loss (red). However, the change will be effective only for new portfolios created from the new template. If you wish to re-apply them to an existing portfolio, you must create a new Portfolio, into which the existing is then imported.

Function corrected: Backup of secondary portfolio

The potential for data loss existed in previous versions. If, during the same session, another portfolio was selected, using the function "Select Portfolio", discarding any changes could lead to the second portfolio being overwritten with a wrong set of data. This program error has now been repaired.

Function changed: Report will show Holdings as at the end of specified period (if any)

This requires recalculating of all transactions up to the selected end date, which may take several minutes. If no date is specified, the current Holdings are reported with the latest available prices.

Function corrected: Company Tax Rates

As of July 2001, the Company tax rate, used to compute Franking Credits, is 30%, not 32%.

Function changed: Trades Report shows additional information for each trade-
number of days in trade,
weekday entered and weekday exited trade,
coloured indication whether trade was Win/ Loss/ or Draw

Function changed: Default Brokerage is now kept for each Portfolio.

Note that only the monetary amount of the Brokerage charged for the last acquisition or disposal is recorded. In cases where the broker charges a set percentage of each transaction, this function will be ineffective, and the actual brokerage will have to be keyed in as charged.

Function changed: After every 5 uses (calls) of the Manager, "Exit" prompts for a backup to be taken. It is recommended to select a removable disk – such as A:\ or a Zip disk. Once a choice has been made, the program will "remember" and suggest the same path in future.

No special Restore is provided. Should the need arise, Windows Explorer or "My Computer" can be used to create the required folder structure and move any damaged or lost files back.

Function changed: Calculating the cost of stocks

A new system-wide option allows the choice of carrying the value of stock inclusive or exclusive of Brokerage, GST, and sundries. The standard method is (used to be) adding all trading costs into the price at which stock was bought.

Replying "OK" when asked: "Do you want to include Brokerage etc in all Transaction Costs?" will leave the status quo. Replying "Cancel" will carry only the buy or sell price. In either case, however, each cost component (Brokerage, Stamp Duty, GST, etc) will continue to be recorded and tallied in reports.

Function changed: Importing latest prices from ASX website

Before a report is run, the program prompts for importing the latest prices from the ASX. Accepting the import will ensure that the Holdings report shows the most current values. If you maintain more than one portfolio, the import needs to be performed for each portfolio separately.

Function changed: Some improved Error Handling

On certain unexpected errors (other than data corruption or obvious input errors) the previous versions would use standard Excel error handling. The new version will give a brief message that the last attempted action led to an unspecified error. It will be possible to retry or - if the error is more persistent - exit the program without accepting the changes.

The Portfolio Manager will import Portfolios from the Market Analyser™ of Trade Tech, although its basic functions can be used without any prerequisite other than a licensed copy of Microsoft™ Excel® - version 97 or later.

The system consists of –

• One Excel Macro Program, Portfolio.xlm
• One Data Model, PortfolioTemplate.xls
• One User Guide, Portfolio Manager.doc

The template forms the basis for any Portfolio. On the same PC, any number of Portfolios can be managed; the only condition being that each copy of the program is licensed for one person or one company only.

Some implementations of Excel may take exception to Excel macros. Being in effect a program that can read and write, even delete files on your disk, caution is advisable. Consequently, when you start Portfolio.xlm, Excel may require confirmation that this macro is allowed to run. Some installations have a tick box that allows this choice to be made permanent. Others provide this function only as an option in the Security setup ("low security level").

The program is distributed as a self-extracting executable zip file. If the defaults are accepted and extracted to C:/, the following folder structure will be created: -.

Portfolio.xlm and PortfolioTemplate.xls are saved in C:/Program Files/Portfolio Manager/.

The Users Guide is stored in C:/Program Files/Portfolio Manager/Documentation/.

All data files go into C:/Program Files/Portfolio Manager/Data/.

Please note: If you elect to implement the Portfolio Manager in different folders, you must create the Program and Data folders before you run Setup.

1. Setting up

Program functions and layout are protected. Therefore, the chance of the program being accidentally overwritten is minimal. The first time the program is called, it installs itself in a nominated program folder. At the same time, registration details locked in. These are name and ABN of the Company, for which the Portfolios are held, as well as the folder names for program and data.

The ABN is optional. Both Company name and ABN are used to identify any printed report. It is for this reason that care should be taken to key those data properly. No edit function has been provided to correct any mistakes later.

Two folder names (full path) are required –

> Location where the program is to install itself (NOT the temporary location where it has been saved when downloading from the Internet or e-mail attachment)

> Location where the various Portfolio data are to be held.

The first Setup screen looks like this:



Figure 3.1.1: Setup Dialog

If you are upgrading from a version older than v4, you may be asked "Do you want to record GST as a separate component?" Replying "OK" has the same effect as ticking the box in front of "Keep Extra Column for GST?" - The first time you call an existing portfolio, the system will extract any GST component contained in Brokerage and place it into a separate column. It will also maintain any Brokerage net of GST and expect the GST to be entered separately. Reports will then show the GST components of buy or sell transactions separately.

Likewise, "Do you want to include Brokerage etc in all Transaction Costs?" will set the switch that causes Transaction costs such as brokerage, GST, etc to be added to ("OK") or kept separate from ("Cancel") the price of a transaction.

2. Selecting a Portfolio

When the system has just been installed, no Portfolio will be defined. Hence, the next screen will ask for one. Initially, the only attribute of concern is the File name:



Figure 3.2.1: Specify Portfolio Name

A Portfolio needs a name to describe it on Reports.
It is important to select a valid file name. Although Windows may accept certain non-alphanumeric characters as part of a file name, some functions of the Portfolio Manager could fail. Problems have been encountered specifically with single and double quotes, ampersands, and exclamation marks.



Figure 3.2.2: Portfolio Description

Alternatively, when there is already one or several portfolios in the Data directory, any one of those can be selected.



Figure 3.2.3: Existing Portfolio

3. The Task Selector

Once a Portfolio (new or existing) has been identified, the system displays the Task Selector:



Figure 3.3.1: The Task Selector

The version number may differ from the one shown here. The number preceding the decimal point indicates a major new version; the release number after the point, the level of error correction and/or minor "cosmetic surgery".

If, at any time during a work session, you decide to look at another spreadsheet (eg to check or manually alter a transaction) you can return to this task selector via the Windows list on the Excel menu bar. Click on the word ‘Window’ (in front of ‘Help’) and then click on ‘Portfolio.xlm’.

The following chapters describe the individual functions that are available. Please note that the program ‘learns’ in the course of your using it. That includes especially the full names of shares, options, warrants, etc. You need to provide the company name of a share code only the first time you deal with it in the current Portfolio. (Note, however, that options or warrants are individual entities in their own right. Therefore, even if you have been using WAN earlier, WANO and WANP must also be defined on their first usage.)

For various reasons, it is also essential that you enter all transactions for one company in strict date order. In particular, this implies that you must own all the shares that pay a dividend when you enter that dividend, even though you may have sold some or all of the shares shortly after "ex div" date.
Suggestion: Record the sale (with the correct date of disposal) only after recording the dividend.

4. Acquiring Stock



Figure 3.4.1: Share Acquisition Dialog

The first cell to fill is ‘Effective Date’. As the system "learns" and keeps tally of your holdings, it is essential that you maintain the sequence of events. For example, you cannot enter the exercise of a call option before you have entered its acquisition.

Buying Shares



Figure 3.4.2: Buy Shares

A simple Buy acquisition: Only fill in the relevant data, then hit the ‘Enter’ key or click ‘Check’. Note: If you tick the "Stamp Duty" box, the program will calculate 15c per $100 (abolished in 2001)



Figure 3.4.3: Process Share Purchase

All data have been accepted, the share name has been filled in (we have been dealing with BLD before), and cost and stamp duty have been calculated. If you notice an error you can "oops" back and correct it. You can overwrite the Stamp Duty amount here.
You can either enter the ‘Cost per Unit’, leaving the ‘Total Cost’ empty, or vice versa. In each case, the program will calculate the missing value. If you are satisfied, click on ‘Process’.

Logical checks are used extensively. An example:



Figure 3.4.4: Please Clarify

Floats and Instalments



Figure 3.4.5: Buying at a Float



Figure 3.4.6: Buying more

We acquire 2000 Telstra 2 when they are floated, 2000 more when they are cheap.

Note that the program "knows" that IPOs don’t attract stamp duty. (But if you insist, you won’t be stopped from entering an amount.) The program will also "learn" how much Brokerage you usually pay. This amount will be pre-filled into the Acquisition and Disposal panels, saving you re-keying. However, only one flat amount – the one applied to the last transaction – will be inserted. Provision for percentages and calculations that are more complex, is not provided. Such cases must always be entered manually.



Figure 3.4.7: Instalment



Figure 3.4.8: Conversion

We acquire 4000 TLS and "trade-in" 4000 Telstra 2. Note that codes are accepted in upper or lower case, and always converted to upper case. Note also the ‘Original Cost’ of our 4000 TLSCB. As we selected brokerage etc to be part of the costs (see figure 3.1.1), these are included.

5. Selling Stock



Figure 3.5.1: Straight Sale



Figure 3.5.2: Process a Sale

If you leave ‘Price per Unit’ and ‘Total Consideration’ empty, the transaction is considered a write-off (hopefully a rare event.)

Stamp Duty is again calculated when you "Check", but you may overwrite before "Processing.

If you exercise a Put option or warrant, or any two stapled items, you must enter the second item’s details on the right-hand side. The program supplies value and original cost.

Your last used brokerage will be pre-filled into the data entry panel, saving you re-entry. It won’t be necessary to erase brokerage if entering a write-off; the system is smart enough to do it for you.

6. Short Selling



Figure 3.6.1: Short Sale



Figure 3.6.2: Process a Sale

If you try to sell stock you do not own, a warning pops up. However, by ticking the box "Short Selling?", it will be processed. You can also short sell stock you own. Simply tick the box "Short Selling?".



Figure 3.6.3: Short Selling flag missing

In your Holdings sheet, short stock is displayed in red, and the quantity flagged with an "S".



Figure 3.6.4: Sold Short

The values (Consideration and Total) are also in red, because you owe the amounts.

The next time you buy any number of this stock, it is considered a short cover; ie the bought quantity comes off your short holding. Let’s assume you buy 5,000 at $5.00 on 28/9/2000:



Figure 3.6.5: Covering the Shorts?

If you confirm this question, the Short Holdings are updated.



Figure 3.6.6: Covered part of the Shorts

If you did NOT confirm (Esc or Cancel), the transaction would result in one short and one long position of the same share.

7. Recording Dividends



Figure 3.7.1: Enter Dividend



Figure 3.7.2: Process Dividend

If you enter dividends at the time they are paid, the program fills in number of shares and Total Credit for you. The Franking Credit is also calculated if you provide the franking rate.

Note: you must always check that those "assumptions" are matching your documentation. If you enter a dividend after you have sold all or part of the shares, you must update the number of participating shares, as well as Total Credit, and Franking Amount.

Re-Investing status will be ‘remembered’ for each share. (You can also reset it.)



Figure 3.7.3: Enter Dividend



Figure 3.7.4: Re-Invest



Figure 3.7.5: Process Dividend Reinvestment

For re-investment, simply "tick" the ‘Re-Investing’ flag, and in the Acquisition Menu, you simply provide the number of shares received.

Note that the dividend payments, including franking credits, are kept for your tax return. The newly acquired shares will be added to your holdings, influencing the cost base of the total holdings.

8. Report Generator

After all transactions and dividends have been entered, the Report Generator lets you import the latest prices of your holdings (20 minutes delayed) directly from the ASX. Your PC must be connected to the Internet if you select "OK".
You may instead update the prices manually (worksheet "Holdings", column "Value/Unit"). (For users who have some experience with Excel.)



Figure 3.8.1: Price Import



Figure 3.8.2: Report Filter

A number of reports are available.

Transactions reported on can be restricted to various periods:

Current and last Quarter,
Current and last Financial Year, and
Current and last Calendar Year.

If that is not enough, a free-form date range can be entered, or the report can be requested for "all there is".

In addition, an ‘X-code filter’ can be applied, which will result in only those transactions, dividends, and holdings being reported that refer to one company only. Note that warrants and options will be included, which means only the first 3 characters of an X-Code are considered.

If one of the first six options is selected, the date range will be computer-generated, discarding any dates that might have been keyed into the fields ‘from’ and ‘to’.

There are 4 reports in any one set. Depending on the filter(s) specified, some of them may be empty or meaningless. However, even if the system comes up with an error message, there is no cause for concern. The reports are created from a copy of the "real" Portfolio, which is not saved to disk.

Each report is displayed ("Print Preview") on screen and can be printed or viewed and then discarded.

The reports are -

Trading Success – in tabular form (click for example) as well as a chart (click for example)
This report displays all closed trades that fall inside the selected period and concern the selected stock. It shows date sold, acquisition cost (total, if several lots were acquired), date of first and last acquisition, total credit, and profit (or loss) in $ and % terms.
If you have acquired several parcels of the same stock over time (eg using dividend re-investment schemes), your Accountant may want to recalculate the cost base for Capital Gains Tax purposes. In such a case, a report filtered on the company’s X-Code with ‘No Date Filter’ will be useful.
In case of Short Selling, entry and exit dates are reversed.

Dividend Records – in tabular form as well as a consolidated chart (click for example), as per specified filter(s)
Reports date of payment, dividend per share, total credit, franking rate, applicable (Company) tax rate, and amount of franking credit.

Transaction Records – in date order, as per specified filter(s) (click for example)
Reports X-Code, name, and number of securities coming in and going out, details of costs, debit and credit components, totals, and profit (loss)

Current Holdings – as at the end of the reporting period, if specified; otherwise current date (click for example).
Reports X-Code, name, number held, total amount paid, as well as average cost (with or without fees and charges – refer special note v9r1), period when acquired (from date, to date), and optional extra: present value. A flag shows whether a Dividend Reinvestment Plan is currently in force.

9. Importing an External Portfolio



Figure 3.9.1: Importing

If Portfolios have already been defined in the Market Analyser or an earlier version of the Portfolio Manager, you can import them into the Portfolio Manager

The source can be either a Portfolio of the Market Analyser or an earlier version of the Portfolio Manager. (Note that MA2000 must provide a ‘Copy’ function.)

If you select MA2000, any file name picked from the bottom part will be ignored.

 

If you select an external Portfolio that contains a duplicate transaction, an alert is displayed.



Figure 3.9.2: Exit to avoid double up

The macro explains in detail how to go about it.

 

Figure 3.9.3a, b: Importing a Portfolio from the Analyser

As each Portfolio maintains its individual list of company names, the Converter makes suggestions that require an answer. Try not to use too long names as the longer the name, the smaller the font size in reports.



Figure 3.9.4: Asking for Security Name

If the MA Portfolio record does not provide sufficient details, missing information is requested with an appropriate question, such as this.

Type in the answer and click OK.



Figure 3.9.5: How many Securities?

Some transaction types may not have an exact match and will therefore be impossible to fully automate. The Market Analyser's Portfolio has problems with Write-offs or off-market transactions (without Brokerage) as it will not accept zeroes in certain fields. If those had been "fudged" into the Analyser, such transactions will require manual intervention later.

In the Market Analyser, Dividend payments are shown without the exact Franking Credit. Purchase and Sell transactions do not show details of Brokerage and Stamp Duty. The Conversion process will make "educated guesses" about those values, but they require checking if accuracy to a cent is needed.

If an unprovided-for transaction is encountered in an Analyser Portfolio, a message will be displayed, showing as much detail as possible, but advising of the need to manually add this transaction. This will usually require a "Rebuild" after any such leftovers have been added.

Note that after importing the Market Analyser Portfolio, a Rebuild runs automatically. See chapter 3.8. Note also that you can import several external Portfolios into a single new Portfolio. The Portfolio Manager will warn, but not prevent you from importing the same set more than once. If you did by accident, you can either delete the additional transactions manually (see next chapter), or discard all changes made during the current session. (A session, in this context, comprises all transactions that you entered since selecting the current Portfolio. See chapter 3.2)

10. Correction and Rebuilding

If a report shows an error, eg an incorrect purchase price or dividend date, amending the spreadsheet in question can repair this. However, this does require a level of proficiency in the use of Excel. If this is not given, it may be prudent to "Discard Changes" and reset to a version that is known to have been correct, then re-key the discarded transactions.

For manual amendment, the program must be "Waiting for next Command"; ie data entry of transactions or dividends, and reporting must have been exited. This is usually the case when the Task Selector is on display.

The current Portfolio spreadsheet can now be activated by selecting its name from the ‘Window’ option on Excel’s menu bar.

The Portfolio workbook file has five sheets, named Lookup, Trades, Dividends, Transactions, and Holdings. Lookup contains various control tables that must not be tampered with. Especially the transaction types, tax rate tables, and company names are critical in that they must match references from within the program.

Only Dividend and Transaction records may be manually corrected.

If Trades or Holdings appear incorrect, any alterations in their sheets will be ineffective, because they are rebuilt from Transaction records.

Transactions are activated by clicking on the tab "Transactions" at the bottom left border of the spreadsheet.



Figure 3.10.1: Portfolio Worksheets



Figure 3.10.2: Transaction Selector

Transaction and Code columns allow only a well-defined number of entries.

To find the correct one, click on the "handle" (a little square in the bottom right corner) of a cell, and a "drop-down arrow" will be displayed to the right of that cell.

Clicking on that arrow will drop down a list of allowed values, one of which can be selected by simply clicking on it.

By editing any values, you take sole responsibility for the result. While the program will check entries and attempt to correct obvious mistakes in forms described in previous chapters, direct entry bypasses any such checks.

When editing is finished (or at any other time), the Trades and Holdings worksheets can be rebuilt from the Transaction sheet. Simply click on the "Rebuild Portfolio" button on the Task Selector.

You can monitor each transaction as it is processed. Click OK for yes, Cancel for No.

Trades and Holdings will now be wiped clean and all transactions will be re-applied in order of their date. Note that dividends do not take part in the Rebuild.



Figure 3.10.3: Monitor Rebuilding

If you opted to monitor each transaction, you will be shown each one as it comes up. However, it will make no difference whether you press the ‘Check" or "Exit Now" button. Should you still discover an error, make a note of it (date, x-code, and value as it should be), and correct it after the rebuild run as explained above.

11. Backup and Restore

Every time you select a new Portfolio, a backup copy is automatically generated. At the end of a processing run, or whenever a new Portfolio is activated, the program will ask



Figure 3.11.1: Commitment

If you answer No (click ‘Cancel’) then the previously saved backup copy is retrieved and overwrites the Portfolio file.

This will in effect "undo" all changes made during the current "session".

You can also "Discard Changes" at any time during a session. This option resets the currently active Portfolio to the Backup copy, which was saved at the date and time that shows just behind the letters "BkUp" in the listed backup file names.
(Note: These "backup" files are not to be confused with a system backup offered after every five runs.)

After every 5 uses (calls) of the Manager, "Exit" prompts for a backup to be taken.



It is recommended to select a removable disk – such as A:\ or a Zip disk. Once a choice has been made, the program will "remember" and suggest the same path in future.



If, during the backup, the target runs out of space, a warning will appear; in case of a removable diskette (e.g. A:\) simply replace the full floppy and insert a blank one before continuing. On a network drive or hard disk, you will need to create more space before continuing. If that is not possible, it is safe to exit and "fix the space problem" first before calling the Manager again.

Upon completion,



you can safely remove the diskette.
No special Restore is provided. Should the need arise, Windows Explorer or "My Computer" can be used to move any damaged or lost files back.

12. More House-Keeping

Over time, the number of Backup files will grow and become a burden on your disk. Clean up allows you to delete superseded backups.



Figure 3.12.1: Clean-out Backup files

Backup file names are made up of the letters "BkUp", followed by the date and time when the file was created. (This requires that your PC has its date and time set with reasonable accuracy.) The time stamp is followed by the name of the Portfolio concerned. The file extension is ".bak"

You can delete several files in one loop, selecting one and hitting "Delete". The program will only return to the Task Selector when you click on the "Exit" button.

This program and all its components are subject to Copyright. It is distributed "As Is" without any expressed or implied warranty. This version may be installed once on one PC and used free of charge for a period of four weeks after installation. If you wish to continue using the program beyond that time, you are required to register your copy.

Registration is available at a once-off payment of $49.50 (inc. GST). It gives the license holder the right to continued use of the program. Holder will need to attach file C:/upf.ini to an e-mail to This Address and will receive a personal PIN Number with a new upf.ini in return.

Registered clients will also receive any future upgrades free of charge.

If structural changes become necessary in the interest of further enhancements, I shall endeavour to provide conversion programs so that existing portfolios will not need to be re-keyed.