Fund Manager
PORTFOLIO MANAGEMENT SOFTWARE
Contact Us

Attribute multi-currency cash transactions

Questions about updating prices or transactions in Fund Manager

Postby vic-t » Sat Apr 18, 2020 8:43 am

Hi there,

I just started evaluating FMA today.

In a first step, I imported the positions per last year end and tried to then import all transactions since then. I seem to have hit my first hurdle.

The portfolio I work with not only have investments in multiple currencies but also multi-currency cash accounts. Investments are always debited from the respective currency account. So, if I buy a Canadian stock, the CAD account will be debited, for US stocks the USD account, and so on. Moving money between cash accounts is always done by way of currency exchange, naturally. So this can have a significant impact on the overall performance.

I understand it's possible to manually change the cash account for each transaction but that would be too time consuming. Is there some way of either FMA doing this correctly right from the start or to at least correct the cash account for multiple transactions at once?

Thanks,
Vic
vic-t
 
Posts: 3
Joined: Sat Apr 18, 2020 8:07 am

Postby Mark » Sat Apr 18, 2020 8:49 am

Hi Vic,

How are you importing transactions? For this to happen automatically you'd want to set up different sub-portfolios, one for each currency. You'd put investments into each sub-portfolio by currency. Each sub would have a different default cash account, for that currency.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11253
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby vic-t » Sat Apr 18, 2020 9:23 am

Hi Mark,

Thank you for the quick reply, on a Saturday no less.

I'm using Interactive Brokers and to get set up quickly, I just downloaded the QFX file with the following header:

OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252

If we need more or different information than provided by the QFX, IBKR offers so called Flex Queries which allow for the creation of very individual reports that can be exported to CSV. All I'd need is a detailed list of fields that should be retrieved.

Be that as it may, I don't see a way of extracting reports that only refer to transactions in one currency. So, even if I create the sub-portfolios in FMA for each currency, how would I import the data correctly?

And as a follow-up to all that, I assume I'd still be able to view and manage the consolidated portfolio in FMA?
vic-t
 
Posts: 3
Joined: Sat Apr 18, 2020 8:07 am

Postby Mark » Sat Apr 18, 2020 5:34 pm

Hi Vic,

If you have the ability to get a download that has the cash side of the transactions reported explicitly you can then not use the default cash account feature in FM, and have the cash transactions go into their separate cash investments. For example, if you have a buy of stock ABC in currency Y, you would need to transactions. The buy of ABC and the sell of cash Y. If you only have the buy of ABC in your importing data then you'd either need to rely on the default cash account feature, or manually record the cash side of things.

If you don't have a lot of new investments, you could manually make the investments before importing, so they are in the correct sub-portfolio, based on their currency, before importing. The other option is to not use the default cash during import, do the import of all the investment transactions, move any new investments into their currency subs, and then use "File / File Operations / Re-Create Default Cash Transactions".

There are probably several ways you can go about this, hopefully this gives you some ideas.

Yes, you can view reports/graphs for any level of your sub-portfolio hierarchy, so you can report on a level that includes all these currency subs.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11253
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby vic-t » Sun Apr 19, 2020 6:15 am

Hi Mark,

Thanks again for your answer on a weekend.

I just spent the last several hours examining the QFX file in detail and doing some tests. I came to the conclusion that the QFX file provided by IBKR must be considered broken. Here is how a buy transaction looks like:

Code: Select all
   <BUYSTOCK>
   <INVBUY>
      <INVTRAN>
         <FITID>xxx123yyy</FITID>
         <DTTRADE>20200302030107.000[-5:EST]</DTTRADE>
      </INVTRAN>
      <SECID>
         <UNIQUEID>GB00B24CGK77</UNIQUEID>
         <UNIQUEIDTYPE>ISIN</UNIQUEIDTYPE>
      </SECID>
      <UNITS>55</UNITS>
      <UNITPRICE>70.963</UNITPRICE>
      <COMMISSION>3.6705</COMMISSION>
      <TAXES>19.514825</TAXES>
      <TOTAL>-3926.150325</TOTAL>
      <CURRENCY>
         <CURRATE>1.2235</CURRATE>
         <CURSYM>GBP</CURSYM>
      </CURRENCY>
      <SUBACCTSEC>CASH</SUBACCTSEC>
      <SUBACCTFUND>CASH</SUBACCTFUND>
   </INVBUY>
   <BUYTYPE>BUY</BUYTYPE>
</BUYSTOCK>


In spite of the final amount being withdrawn in the investment currency from the cash account in the same currency (i.e. GBP), all numbers (price, commission, total) within the BUYSTOCK section have been converted to the base currency of the account (i.e. CHF). So, the I actually bought the stock at a price of GBP 5 which translates to CHF 70.963 at the rate of 1.2235. I have the impression that this is not what your software would expect here.

It gets even worse with how cash conversion transactions are described. Here an example of a purchase of GBP against CHF:

Code: Select all
<INVBANKTRAN>
   <STMTTRN>
      <TRNTYPE>OTHER</TRNTYPE>
      <DTPOSTED>20200416202000.000[-4:EDT]</DTPOSTED>
      <TRNAMT>-3873.49785</TRNAMT>
      <FITID>xxx123yyy</FITID>
      <MEMO>CASH TRADE: GBP.CHF</MEMO>
      <CURRENCY>
         <CURRATE>1.0</CURRATE>
         <CURSYM>CHF</CURSYM>
      </CURRENCY>
   </STMTTRN>
   <SUBACCTFUND>CASH</SUBACCTFUND>
</INVBANKTRAN>


Since the whole transaction is described only in the base currency and no conversion rate is provided, it's not possible to calculate how many GBPs were actually converted. And no, there is no second transaction describing the same conversion in GBP.

I don't think it's realistic to proceed with the QFX files, too many issues.

You mention on your web site that it's possible to request a specific reporting format for your software from IBKR if you have an advisor account which I do. So I made the request and am now waiting for them to reply. Will this format help me achieve my goal in a less complex way?
vic-t
 
Posts: 3
Joined: Sat Apr 18, 2020 8:07 am

Postby Mark » Sun Apr 19, 2020 9:07 am

Hi Vic,

You're right, that OFX format will not work very well. FM does need all transaction data to be reported in the currency of that investment.

Yes, I believe IBKR's "Generic" transaction format for their Advisors will work better. It does report currency information. It is still not "simple" to get all the currency information working with cash, but it should be provided in a better manner. Once you get some files, and take a look, let me know if you have questions.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11253
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Arrow » Wed Apr 29, 2020 4:07 am

vic-t wrote:Hi there,

I just started evaluating FMA today.

In a first step, I imported the positions per last year end and tried to then import all transactions since then. I seem to have hit my first hurdle.

The portfolio I work with not only have investments in multiple currencies but also multi-currency cash accounts. Investments are always debited from the respective currency account. So, if I buy a Canadian stock, the CAD account will be debited, for US stocks the USD account, and so on. Moving money between cash accounts is always done by way of currency exchange, naturally. So this can have a significant impact on the overall performance.

I understand it's possible to manually change the cash account for each transaction but that would be too time consuming. Is there some way of either FMA doing this correctly right from the start or to at least correct the cash account for multiple transactions at once?

Thanks,
Vic


Hello,
I'm a a prospective customer and I was wondering about this issue having an account with IB, though not an Advisor one.
I would like to point out that with IB you can buy stocks in a currency different from your base one (in the example Vic reported CHF), stocks are valued in the different currency i.e. GBP and after they are sold your account receives Gbp ; it's up to you if you want to convert again GBP in CHF but if you leave all cash and no positions open in your account you will have a balance that changes every day due to varying exchange rate CHF/GBP for the amount in GBP that you received.
In other words IB considers that you have always only a single account, also because it must compute margins and other kind of checks on your whole wealth altogether.
Did you solve it?
Thanks for your answer
Arrow
 
Posts: 35
Joined: Wed Apr 29, 2020 3:40 am


Return to Prices and Transactions

Who is online

Users browsing this forum: No registered users and 9 guests

FundManagerSoftware.com | Search | Site Map | About Us | Privacy Policy
Copyright © 1993-2024 Beiley Software, Inc. All rights reserved.
cron