Anyone using FM with Interactive Brokers for foreign stocks?

[caffellatte]

I am new to Fund Manager, trying to get it to work with Interactive Brokers, where I am buying foreign stocks in local currencies.

I understand that IB does not have an OFX server, so I downloaded a QFX file of my year-to-date transactions. (Now you can download a year's transactions at a time. According to an old post, at one time you could only download 31 days at a time!) I discovered that share price and market value of positions had been converted to USD. The entry for cash in my account did not correspond to anything I could make sense of: the first time I imported, it was about $15K, and the second time I imported the same QFX file (after closing the portfolio) it was about −$3K. When I double-clicked on the name of a Singapore stock, FM said the market value was "SGD815.40", although in fact the market value is USD 815.40.

The fun really started when I tried downloading prices. Most of the servers I tried didn't work at all, even when I changed the symbol to the one shown on the service's website. Wall Street Journal was one that failed to download prices. (Are they down on Saturday night?) I finally got Yahoo to work. But then the prices downloaded were in local currency.

Then FM displays a list of positions, showing the value of each in local currency, which means it cannot total the value of the account. WHAT A MESS!

At this point, the only way I think I can get FM to work with my IB account is to only download information from IB. That means that whenever I want to check prices, I must go to the IB site and manually download a QFX file and import it into FM. That way, all values will be in USD.

Has anyone found a better way to make FM work with an IB account? (As may be obvious by now, I am not an advisor. I understand there is better integration of FM with an advisor account.) It would be great if FM knew what currency was referred to in the different downloads, if it knew that the IB download is in the account base currency (USD), and the Yahoo download is in local currency, and even better if FM could convert local currency to USD and display values in USD in a separate column, so it could then total the value of the account. But I suspect there are not enough users to justify the work to accomplish this.

[caffellatte]

I discovered that if I change the currency under Properties for each stock from the local currency to USD, FM will now display the total market value for the account when I go to Reports | Portfolio Value. And when I click on the Singapore stock mentioned in my last post, FM displays the market value as "USD815.40", which is correct.

Why did FM add a 4-digit prefix to my IB account number when it named this sub-portfolio?

[Mark]

Hi caffellatte,

First, have you watched the tutorial on tracking multiple currencies? It will explain how FM handles currencies, and maybe clear up some of the confusion.

When you import prices or transactions, everything is entered in the currency assigned to that particular investment. You can then optionally display the value either in that investment's assigned currency, or exchanged into any "default" currency you specify. (With currency templates, you can even have multiple/different default currencies.)

By default, FM won't sum values across currencies. If you display everything in the default currency, then you can see a sum.

You'll want to set up exchange rate investments to keep track of the exchange rates between each currency you're using, and your default currency. All of the above is described in more detail in the tutorial. Please also see the documentation on multiple currencies.

I would also suggest watching the tutorial on tracking cash.

When you import a new account from an OFX/QFX file FM will name the sub-portfolio with the format: <Broker ID> - <Account #>, as read in from the importing file. You can rename it to anything you want by right mouse clicking on it, and choosing "Properties...".

Getting started using FM can be a bit of a learning curve, and it is even more complicated when you have multiple currencies, but hopefully the above will help get you started.

[caffellatte]

I will re-watch the tutorial and I will study the web page on Currency Settings Dialog, which I had not seen.

Will they address the following issue? When I first download the QFX file from IB, before I adjust anything, FM "thinks" the prices and market value figures are in local currency, even though they are in USD. (Evidently, the information about the stock included in the download tells FM that it trades in the local currency, even though the values in the file are in USD.) For example, on 3/26/20, I bought 4000 shares of Chen Hsong Holdings (ticker 57 on Hong Kong Stock Exchange). I had also purchased 4000 shares on 1/28/20. When I first imported the QFX file, FM said the total market value (now) was HKD 2157.22. When I changed the currency in Properties for this investment, FM said market value was USD 2157.22, which is correct. FM says I bought 4000 shares at 0.236 and that I received dividend payments on 9/16/20 of 39.23 and 17.55. FM says the current price is 0.27, which is true in USD.

From my experiments so far, if I download the current price for Yahoo, it will be be the price in HKD, which is 2.09. So FM will think the share price has increased since I bought the shares from 0.236 to 2.09!

I will study the materials you recommend, but I am wondering whether they will show me how to set things up so FM knows that the values it receives in QFX files from IB are in USD and the prices downloaded from Yahoo are in HKD.

[Mark]

Hi caffellatte,

Is your "local currency" HKD?

FM will create the investment in the currency specified in the importing file, or your FM default currency if it isn't specified. In an OFX/QFX file there is an account default currency (CURDEF), and each transaction can also specify a different currency with the CURSYM entry. So, if a transaction is reported without a CURSYM, FM will create it with CURDEF if available, or if that isn't specified, it will resort to your default currency. To reiterate, the order of priority goes CURSYM, CURDEF, <FM Default>.

Once an investment is created in a certain currency, all prices/transactions should be entered in that currency. So, if you have an investment in HKD, make sure you're retrieving prices in HKD for that investment.

To see how your QFX file is reporting currencies, you can open up the QFX file you're importing and look, or look at the "xmldump.txt" log file (Help / Logs / Transaction Retrieve (Parsed)...) after importing for a more human readable format. Look and see what is set for CURDEF, and CURSYM on each of your transactions.

The prices reported in the INVPOSLIST section are assumed to be the same currency as the investment. (If for some reason this is not the case you can turn off the option to updated prices when retrieving transactions.)

If this isn't clear, and you want more help, you could email us the xmldump.txt file, along with a specific case example you want to look at.

[caffellatte]

Thanks, Mark. I am thinking that even if I turn off the option to receive prices when receiving transactions from IB, IB will still report the price at which I bought the shares in USD, and it will also report the amount of dividends in USD.

BTW I did send a message to IB asking if it is possible for me to arrange to get QFX downloads with values quoted in local currency. I imagine it will take several days to hear back, if I get an answer at all.

Yes, for the example I gave, local currency is HKD. My other three positions right now are in AUD, CAD, and SGD. I picked the Hong Kong for the example because there is the greatest difference between values in local currency and USD.

I am glad to hear that it is possible to set things up so I can shift back and forth between looking at current market value in local currency and USD (if I understand you correctly).

I have been closing the portfolio and downloading again when things get messed up. What I will do now is leave the portfolio I downloaded as is and download it again with a different name, so I can experiment with settings in that one.

I have just reviewed the QFX file. Indeed, it includes the entry

<CURDEF>USD</CURDEF>

For the position we have been discussing, it says in the INVPOSLIST section

<SECID>
<UNIQUEID>BMG208741063</UNIQUEID>
<UNIQUEIDTYPE>ISIN</UNIQUEIDTYPE>
</SECID>
<HELDINACCT>CASH</HELDINACCT>
<POSTYPE>LONG</POSTYPE>
<UNITS>4000</UNITS>
<UNITPRICE>0.269652</UNITPRICE>
<MKTVAL>1078.6072</MKTVAL>
<DTPRICEASOF>20201204202000.000[-5:EST]</DTPRICEASOF>
<CURRENCY>
<CURRATE>0.129</CURRATE>
<CURSYM>HKD</CURSYM>
</CURRENCY>
[Emphasis added]

Note that the code above is refers to only 4000 shares. There is what appears to be a duplicate section for the other 4000 shares purchased in January.

So IB is telling FM that the values reported (current price and total market value of the position) are in HKD, but the values are actually the amounts in USD? What a company!!

Should I complain to IB that the currency shown as "CURSYM" should match the values reported?

Thanks for all the help. (Your approach to customer service is the inverse of IB's!)

[Mark]

Hi caffellatte,

So, your account default currency is being reported as USD, and the security with CUSIP of BMG208741063 is reported to be a HKD price, but you're saying it is really a USD price? What about the transactions, what currency are they reported in? FM actually isn't using the CURSYM in the SECID section, but it sets the investment's currency based on the currency reported in the transaction section (INVTRANLIST). Once an investment is created/assigned to a currency, it is assuming the prices reported are in that same currency.

[caffellatte]

Yes, every single monetary value in the download is the amount in USD. The price per share, the total market value of the position, the commission charged, the amount of the dividend paid—each of these is the amount in USD.

Here is the section referring to the transaction in my example [emphasis added]:

</INVTRAN>
<SECID>
<UNIQUEID>BMG208741063</UNIQUEID>
<UNIQUEIDTYPE>ISIN</UNIQUEIDTYPE>
</SECID>
<UNITS>4000</UNITS>
<UNITPRICE>0.23607</UNITPRICE>
<COMMISSION>2.322</COMMISSION>
<TAXES>1.057496</TAXES>
<TOTAL>-947.659496</TOTAL>
<CURRENCY>
<CURRATE>0.129</CURRATE>
<CURSYM>HKD</CURSYM>
</CURRENCY>
<SUBACCTSEC>CASH</SUBACCTSEC>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBUY>
<BUYTYPE>BUY</BUYTYPE>
</BUYSTOCK>
<BUYSTOCK>
<INVBUY>
<INVTRAN>

The price per share was HKD 1.83. Multiplying by the currency rate shown above we get

1.83 × 0.129 = 0.23607

which is the amount shown in the download as <UNITPRICE> (price per share). The line below the currency rate shows <CURSYM>HKD</CURSYM>, which fools FM into thinking that the price per share was HKD 0.23607, whereas in fact it was USD 0.23607.

[caffellatte]

BTW when I sent IB the message requesting downloads showing values in local currency, I also asked if there was any possibility they would set up an OFX server.

[Mark]

Hi caffellatte,

I think the problem here may be that you've downloaded a QFX file instead of an OFX file. QFX is Quicken's version of OFX, and in my experience the currency is not handled strictly according to the OFX spec. The OFX spec specifically states in section 5.2 that when using CURRENCY the amounts should not be converted to the home currency when reported. Can you try downloading an OFX formatted file instead, and see how it is reported there? I think IB may refer to this as a MS Money file?

[caffellatte]

Selecting MS Money for the download did indeed create an OFX file. Now values are displayed correctly in local currency!! Yea!!

I still have more set-up and testing to do and more questions, but at this point I just wanted to let you know the big problem has been solved. Thanks!

[caffellatte]

Do I understand correctly closing a portfolio at the top of the hierarchy, such as the Master Portfolio, is like closing a file in another program, i.e., I can open that portfolio again in the future, but closing a sub-portfolio DELETES it?

On the web page "Currency Settings Dialog" you write: "Place investments assigned to be an exchange rate in their own sub-portfolio, separate from your actual holdings. This allows reporting on your actual holdings, without seeing the exchange rate investments included in your reports."

Should this sub-portfolio be at the level just under Master Portfolio?

[Mark]

Hi caffellatte,

Thanks for the update on the OFX file, glad to hear that.

You can't close the top level sub-portfolio. If you close any sub-portfolio beneath the top, it is removing it (deleting). Any investments in those subs are also closed.

There are 2 types of data files, there is a *.mm4 portfolio file, and *.dat files. There is one *.dat file for every investment. The *.mm4 portfolio file remembers all your sub-portfolios, preferences, and which investments belong in each sub-portfolio. The .mm4 file is basically your workspace.

A typical sub-portfolio hierarchy might be:

Code: Select all

- Master
  - All My Accounts
    - Account 1
    - Account 2
  - Exchange Rates
  - My Watch List

[caffellatte]

Thanks, Mark.

Then what do the commands File | Open Portfolio and File | Close Portfolio do? It looks like File | Open Portfolio is trying to open a .mm4 file. But if there is only one .mm4 file (Master Portfolio), that doesn't make sense to me.

[Mark]

You can have multiple *.mm4 files. Really, this isn't generally necessary, but you can have separate *.mm4 files if you want.