Captools/net Documentation

Multi-Currency Portfolios

Multi-Currency Portfolios

Previous topic Next topic  

Multi-Currency Portfolios

Previous topic Next topic  

Global investing has also become recognized as a means to diversify investment portfolios against the economic risk associated with the business cycles, which exist in every nation's economy.  Most investors can obtain sufficient global diversification through global oriented mutual funds traded on their local market and/or by investing in foreign shares traded on their local market (e.g. American Depository Receipts on the NYSE).


Multi-National, Multi-Currency Investing


The most sophisticated of investors, however, find value in investing directly in foreign securities through accounts denominated in the currency in which the securities are traded.  This approach offers a wider range of investment opportunities without incurring the high fees of mutual funds.  These accounts may be held at brokerage firms or banks in the country of investment, or may be held at a local brokerage firm or bank in your country or at a brokerage firm or bank in a third country, depending upon the laws of your own country and the other countries involved.  Depending upon how these accounts are established, they may also provide tax advantages.  We recommend that you consult your lawyer and/or tax advisor before setting up such accounts.


Higher level versions of Captools/net, for which the user has selected the multi-currency licensing option, permit you to track investments denominated in more than one currency, with the trading amounts and valuations converted to the portfolio base currency of your choice.  The remainder of this section details how multi-currency portfolios are established and operated.



Security Currency


Captools/net accomplishes multi-currency portfolio management by accounting for transactions in both a Security and a Portfolio currency.  The term Security Currency, refers to the currency in which a given security is purchased and sold, and in which it pays dividends.  A security's Currency must be designated for each security in that security's Security ID record, for example:




Currency Symbols - Note that several different currency symbols are used in the Currency column in the above example.  Some symbols such as the "£" and "€" may not appear on your keyboard, depending upon your keyboard style and computer regional settings.  When you edit the Currency field, use the drop-down box to select the appropriate currency symbol.  If it does not appear there, you can copy from here (£, €, ¥)  or use an application such, as MS-Word, which support special symbols, by using the "Insert/Symbol" command in that application to insert the desired currency symbol onto the application text page.  Then you can highlight the symbol and copy it (using Ctrl+C) to MS-Windows clipboard from which you may paste it into the Captools/net Currency field.  Subsequently, when adding new records to the security list, you should be able to select one of these currencies from the drop down edit box.


If you use "$" character in a currency symbol, you should be aware that Captools/net assumes a price and cost of "1.0" for symbols with this character in the first or last position a symbol or 4 or more characters. This is to allow a simple designation of a symbol as a money market fund.  Thus if you use a symbol such as "AUS$" to represent the Australian dollar, it will always have a forced price and cost of "1.0".  While this is okay if the AUS$ is your portfolio base currency, it will present a problem if it is a foreign currency holding.  In this case we recommend shortening the symbol to 3 or less characters, e.g. "AU$", using another character in lieu of the "$" in the currency symbol, e.g. "AUS#" or place the "$" in the middle of the symbol, e.g. "AU$D.


Each currency symbol that you use should be defined in the Security Id records.  Such record should contain the same currency symbol in both the Symbol and the Currency fields as follows:




Important: If nothing is specified in the Currency field of a Security Id record, or if Captools/net cannot find a Security Id record for a symbol, then it is assumed that the Currency is the same as the Portfolio Currency (see below).  For this reason, it is mandatory that a security record exist for all securities contained, past or present, in any multi-currency portfolio being managed with Captools/net.   It is also important that your currency symbols include at least one character which will not be used in ordinary security symbols, i.e. one character which is not in the set "A" to "Z" or "1" to "9" or the characters "#", ".", "-", "_", "+", or "^".  The reason for this is if the currency symbol containing only alpha characters is embedded in a security symbol, then that security may mistakenly be assumed to be a money market fund in that particular currency.


Foreign Currency Symbol Imports - If your custodian provides you with data files containing foreign currency symbols used in trading, Captools/net may translate these symbols to its own symbols notation (e.g. for Interactive Brokers).  In these cases you should retain the Captools/net notation since the importing script may rely upon the notation to properly import.



Portfolio Base Currency


In multi-currency portfolios, transaction amounts denominated in the Security's currency are converted to the Portfolio Base Currency using a currency exchange rate.  The Portfolio Base Currency is the currency in which the portfolio is valued, in which profits are computed and taxable gains reported.  It will typically be the currency of your own country, although you may choose to use one of the major international currencies for your portfolio currency, if most of your investment accounts are denominated in that currency.


The portfolio currency must be specified for a portfolio at the time the portfolio is created.  This is done by setting the Base Currency field in the portfolio's account record.  This field appears on the Account Mgmt tab, in the Multi-Currency group when the account record is in the Edit mode:




The Multi-Currency controls in the account record can be controlled at the account record level, by checking the Portfolio Computations group box as in the above example.  If the Portfolio Computations group is not checked, then  the Multi-Currency controls may be set in the Program Preferences.


Warning: Once you have designated the Portfolio Base Currency for a portfolio, you cannot simply change it later to another currency, if the portfolio already contains multi-currency holdings.  The reason for this restriction is that all exchange rates would have to be changed for all transactions and valuations in the portfolio, if the Portfolio Base Currency is changed.  If you do need to change the portfolio currency, you should use the Currency Revaluation function described later in this topic.



Reporting Currencies


In addition to converting transactions and valuations from a Security Currency denomination to a Portfolio Currency denomination, Captools/net permits users to generate reports which employ both of these values.  Many reports will also provide you the option to display some figures in a third, typically Global currency, e.g. U.S. Dollar, Euro or Yen.  This can be useful, for example if the base currency is not a global currency.  In order for such global currency reporting to be accurate, exchange rate records must be maintained in the security price history files for the portfolio currency and the global currency.  See Multi-Currency Reports for more details.



Currency Exchange Rates


Currency exchange rates are stored in the security price history records using the Currency Price method.  This method simply stores the exchange rate of the currency relative to an exchange rate base currency, using the Currency Symbol in the Price History symbol field.  When Captools/net requires an exchange rate for a given date, for a given combination of currencies, e.g. currency C$ vs. currency B#, it will look up the price for each currency for that date and divide C$'s price by B#'s price to determine the exchange rate.


Exchange Rate Base Currency - It is not important what exchange rate base currency is used for this method, except that the exchange rate base currency must be the same for all currencies in the data base.  Because of this requirement, the U.S. dollar is probably the best choice for this base currency because it has a long history (unlike the Euro), and will not likely disappear (like some European currencies which may someday be absorbed into the Euro).  Assuming that the base currency is the US Dollar, and C$ is the Canadian dollar, worth 0.75 U.S. dollar, and B# is the British pound, worth 1.80 U.S. dollars, then the computed exchange rate would be "2.4", meaning that 1 unit of B# is exchangeable for 2.4 units of C$.  The global base currency symbol is not explicitly defined by you or within Captools/net, however is implicit in that whoever is your Captools/net data administrator must ensure that price records for currencies are always consistently stated in terms of one currency.


Rate Orientation - All exchange rates must have the same "orientation" relative to the Exchange Rate Base Currency.  This orientation should be the amount of the Exchange Rate Base Currency required to purchase one unit of the object currency.  Thus, if your Exchange Rate Base Currency is the U.S. Dollar, and one Canadian Dollar costs 0.75 U.S. Dollars, and one British Pound costs 1.80, and one Euro costs 1.25, and one Japanese yen costs 0.00909, then these are the amounts that should be entered in the Price History records. Note that in this example, the rate for the Japanese yen is not stated at "110.0" as is commonly the case.  Hint: In order to ensure that you have the correct rate orientation, remember to verify that the rate, multiplied times one unit of the currency, does produce the equivalent value in the Exchange Rate Base Currency.


Frequency -  Exchange rates should be entered at least at the same frequency with which portfolio valuations are performed.  Thus, if you create weekly valuations, you need corresponding weekly exchange rate records of the same dates in the price blotter or price history records for all currencies in which you are investing.


Exchange Rate Downloads - Captools/net may in some cases be able to download currency rates along with scheduled security price downloads.  Any currency rate download scripts provided with Captools/net will use the U.S. Dollar as the exchange rate base currency.


Exchange Rate Symbols - When Captools/net looks up exchange rates it first looks for price records under an artificial symbol constructed from the two currency symbols in the manner of "fromcurrency>tocurrency" with the ">" character separating the two currency symbols (the "}" character can also be used).  If a matching price record is found, that is assumed to be the relevant exchange rate.  If no such symbol is or price record is found then Captools/net looks up the pricing records separately for each currency symbol and computes the ratio between the prices found to be the exchange rate.  In this case if your base currency is "$", then all other currencies would have their prices stated relative to the "$" and the "$" would always have a price of "1.0".



Multi-Currency Transactions


To enter multi-currency portfolio transactions in Captools, select the Multi-Currency option on the portfolio transaction View/Switch View sub-menu:




The multi-currency transaction view permits you to specify transaction amounts in both the Local and Portfolio currency.  In the following example, both U.S. and non-U.S. accounts are opened, and securities purchased and tracked in a multi-currency portfolio denominated in US dollars:




Foreign Currency or Security - Transactions involving securities or currency denominated in other than the portfolio's base currency are colored a pale green in the Symbol and Local Amount fields to indicate this transaction should involve an exchange rate.


Local Amounts - The Commission, Trade Fees, Accrued Interest, and Tax Withheld fields are also colored pale green as in the above example, but only if the amount in these fields is stated in the security's currency, and not the portfolio's base currency.  This is specified via a number of check box fields visible on the far right of the transaction view (horizontal scrolling may be necessary):




These check boxes are automatically set to be checked as soon as a foreign symbol is entered into the symbol field.  If you want to enter a Commission, Trade Fee, etc., denominated in the portfolio base currency, then you must uncheck the applicable check box.


Exchange Rate -The Exchange Rate used in non-portfolio-currency transactions should be the exchange rate at which the transaction actually took place, if the transaction involves the conversion of one currency to another.  However, if a security is purchased or sold in the currency in which it is denominated, with the purchase funds or sales proceeds coming from, or going to, a money fund denominated in the same local currency, then no currency conversion actually occurs.  In this case the Exchange Rate field may contain the day's ending quoted exchange rate from the security's currency to the portfolio currency.


Foreign Cash Accounting - Captools/net treatment of multi-currency portfolio cash is controlled via the Foreign Cash Accounting option, which appears in the Multi-Currency group box on the Account Mgmt tab when the portfolio account record is in the Edit mode:




If this option is selected, then only transactions in securities denominated in the portfolio's base currency will be credited or debited to (Cash).  Transactions denominated in other currencies will be debited or credited to those currencies, whose balance will appear in the position records.


For accounts using "Foreign Cash Accounting", you need to use "BFC" (Buy Foreign Currency) and "SFC" (Sell Foreign Currency) transaction codes to move money between the account base currency and the other foreign currencies.  See examples for these codes at Other Transaction Codes.   If fees or tax is withheld from a foreign currency holding, you must use "MFS" (management fee), "OFS" (other fee) or "TXS" (tax withheld) as appropriate.


If Foreign Cash Accounting is not selected, then a separate currency exchange transaction will typically need to be entered into the portfolio whenever a security denominated in a foreign currency is purchased or sold.



Multi-Currency Valuations


Once multi-currency transactions have been entered in a portfolio, valuations may be created in the normal method by entering a new date in the portfolio or batch valuation records.  The resultant position records are best viewed by selecting the Multi-Currency Positions view from the View sub-menu:




In the Multi-Currency view format, position records can be grouped and subtotaled based upon their local currency by right-clicking upon the S/T Type field in the grid:




Each currency group includes a local currency cash sub-account identified by the local currency symbol appearing in the Symbol field.  One exception is the portfolio currency group, whose cash is designated by the same cash symbol, (CASH), used for non-multi-currency portfolios.  The local currency cash sub-accounts indicate the cash balance held in the local currency as of the position date, if any such balance exists:




Market Price and Exchange Rates: Before leaving this view, all prices and exchange rates should contain valid values.  Both can be retrieved from the price records, if they exist for the position date, by selecting the applicable records and using the Compute icon or the Data/Compute command.  Exchange rates should be identical for all securities carrying the same denomination.



Multi-Currency Reports


Reports for multi-currency portfolios are generated in essentially the same fashion as described in Reports for single-currency portfolios.  The only difference is that with multi-currency portfolios you may wish to customize reports to subtotal on currency denomination and to include fields showing local cost, accrued income, and values.  Other available relevant fields include Currency Gain and Currency Gain%, which report the portion of profits due to exchange rate movements.  See Multi-Currency Reports for more details.



Exchange Rate Gain


Some multi-currency reports offer a field entitled "Exchange Rate Gain".  This is computed as follows:


Exchange Rate Gain {base currency} = Total Period Gain {base currency} - Market Gain {base currency}




Market Gain {base currency} = (End Value {trading currency} - End Shares x Avg Purchase Price {trading currency} ) x Average Exchange Rate  + TradingMarketGain {base currency}




TradingMarketGain = Sum of ( Sales Proceeds {trading currency} - (Shares Sold x Avg Purch. Price {trading currency}) x AvgPurchaseExchgRate))





Base Currency is the currency of the portfolio


Trading Currency is currency in which the security is traded


Average Purchase Price is average price (Share weighted) at which security was purchased in the period


Average Purchase Exchange Rate is the average exchange rate at which the security was purchased in the period.



Multi-Currency Precision


Multi-currency accounting can lead to a certain amount of imprecision due to application of factors and/or divisors to convert one currency to another, and subsequent rounding.  Thus one cannot expect to find that amounts converted from a security currency to a portfolio currency and perhaps to a third global currency can be converted back to the security currency and obtain the exact original value.  Because Captools/net exchange rates are stored at precision of 7 significant digits this is the maximum level of precision you can expect in values computed using exchange rates.



Currency Revaluation


Sometimes, although very infrequently, some users may need to change the currency or the value of the currency in which the account list, security list and portfolio records are denominated.  An example of such a situation is the conversion of many European currencies to the "Euro".  The revaluation of inflated currencies is another situation requiring currency revaluation.  Caution: Before you perform a currency revaluation, you should be sure that all of your data is backed up and restorable.  Also, if your system uses a Data Administrator to maintain your Captools/net data, then access to this function may be restricted to that user only.


Executing the Tools/Currency Revaluation command with the Account List in the foreground view accesses the browser-based currency revaluation dialog:




The fields in this dialog operate as follows:


Old currency symbol - This is the symbol of the currency which is being converted.


New currency symbol - This is the symbol you want to use for the new currency.  If your old currency is being "revalued" as a result of inflation, you may retain the same symbol.


Conversion factor - This is the numerical factor that will be applied to all relevant fields in all records in the selected data documents.  In the example above, a transaction which originally contained "1000" in the Amount field will contain "570" following the conversion.


Decimal precision - This is the decimal precision to which money amounts will be rounded following data conversion.


Account Selection/Filters - You can apply account selection or use the "Filters" control to apply the conversion to only selected accounts.  However, if you apply conversion to only some accounts, you should keep notes to that effect so that you do not inadvertently convert an account more than one time.


Review Data! - Following currency revaluation, you should carefully review the portfolio data in at least several accounts, as well as price and financial history data to confirm that the conversion performed as expected.   If it did not, you will have to restore your original data files and re-execute the conversion.