Importing Investment Transaction

LostCanuck

Hey everyone,

Is there a way to import investment transactions into Quicken Classic Business & Personal? I'm using Canadian Version R53.26, Build 27.1.53.26.

The format from my broker is a CSV file (RBC Dominion).

Thanks in advance

Cheers,

Randy

Arctic Hare

The Canadian version of Quicken has integrated support for importing investment transaction data from a very small number of financial institutions. Importing investment transaction data from RBC Dominion is NOT supported. Quicken does not support have integrated support for importing transaction data in CSV format from any financial institution. However, you might be able to use one of the tools offered by Quicken Pearl Wizard to import CSV data - by way of data transformation - to get it to work if you are so inclined.

LostCanuck

@Arctic Hare - thanks for the quick reply. I assume Quicken Pearl Wizard is the maker of ImportQIF? I’ve used this software to import American Express data and it worked great. I will give it a try

Will Quicken allow me to select the investment account as an import account?

Cheers

Randy

Arctic Hare

https://community.quicken.com/discussion/comment/20404543#Comment_20404543

Yes, re: QPW.

I've never tried importing investment transaction data, but I expect it would work like any other WebConnect import. The Canadian version of Quicken supports WebConnect for investment data for select financial institutions, so I presume it will. The one potential issue that comes to mind is that Quicken might look for the financial institution code to confirm that QIF (Web Connect) is supported for that FI. In which case, you might need to "fake" the financial institution in the QPW application. You'd need to look up the code for one of the FI's that support Web Connect for investment data in the Canadian FIDIR.TXT and use that as your "fake" financial institution.

@Chris_QPW (the one and only Quicken Pearl Wizard) is active on this forum. @Chris_QPW would be a much better resource than me to guide you through how you might use the app to make it work. I've never tried the QPW apps. I'm interested in hearing whether you can make this work as I might try it for my discount broker accounts. Please post back with how this plays out.

LostCanuck

@Arctic Hare i will definitely follow up on how this goes. Probably won’t get to it with the holidays coming. Happy holidays

Chris_QPW

How good it will work really depends on the quality of the data in the CSV file.

The CSV file format only describes how the data columns are separated. What is in those columns has to be mapped to correct data field that will be imported into Quicken. Investment data adds the extra complication that the "security action terms" like Buy, Sell, … are not standardized and as such they have to be mapped to the correct security action while converting the data (ImportQIF prompts for this as it encounters a new security action term.

The default is to export a QIF file, which can be imported into Quicken without worrying about the financial institution Id but is subject to the fact that Quicken Subscription will put it directly into the register without any further processing. This is probably far less of a problem with investment accounts given that you probably aren't matching existing transactions, and the "category" is usually picked by the security action that you happen to use like Dividend Income. But ImportQIF can produce a QFX file too if you are so inclined.

LostCanuck

@Chris_QPW Thanks for the input. I gave this a shot this morning with mixed results. Here is what I discovered so far:

1. I limited the CSV file to only Buy and Sell transactions. I deleted all the dividend, interest etc. transaction.
2. The CSV to QIF worked with minor manipulation. After mapping the headers in ImportQIF, ImportQIF asked me to pick Buy or Sell for each transaction; it didn't recognize the CSV field Activity term of Buy/Sell.
3. The file imported into Quicken, but did not align to the existing account nor did it ask to pick an account. It created a new account with the same title as the one in the Account CSV column
4. Security symbols in the CSV file didn't map the security in the Quicken. It created new securities for each CSV security using the symbol listed in the CSV file. In Quicken, each security has the symbol and title
5. The description in Quicken seems to have matched the number of shares and the unit price. The Share Balance column matched the transaction, however since the security symbol didn't align, the total security count is not correct.
6. In Quicken, the Inv. Amt, Cash Amt and Cash Bal all seem to have been properly updated.

Thoughts?

Thanks again for your help. This would be a great feature until Quicken updates their ability to download investment transactions

Cheers,

Randy

Arctic Hare

Note that it is not a matter of "Quicken updating their ability to download transactions". The matter is whether a financial institution has entered into an agreement with Quicken (and/or Intuit I'm not entirely clear whether the agreement is with Quicken, Intuit, or both since the spin off) to support downloading of transactions to Quicken. This is not a one-ended Quicken decision. Downloads are only supported for specific financial institutions and specific formats according to agreements. Put another way, if you are seeking to get support for downloads for a specific financial institution then you need to petition the financial institution, not Quicken. Quicken would love to have all financial institutions signed up for download capability.

LostCanuck

@Arctic Hare good point. I wouldn't think it would be a stretch for RBC Investing to follow RBC Banking in this space. I will pose the question to them.

Arctic Hare

https://community.quicken.com/discussion/comment/20404977#Comment_20404977

I am a RBC Direct Investing Client. I would be thrilled if RBC-DI elected to support download of investment transaction data to Quicken. That being said, in the past I've had reasons to communicate with RBC (banking) about Quicken in the past and even with considerable effort I couldn't manage to connect with anyone at RBC that would even acknowledge that RBC supports Quicken Express Web Connect. Every person I spoke to at RBC advised me that they don't support it and if I used it and bank accounts were compromised it would be my fault for using EWC. It was a very frustrating experience to say the least.

Chris_QPW

Probably the wrong place to put this reply, but since it is here, I will answer it here.

#1 Not sure why you deleted the other security actions, the ones you listed ImportQIF can certainly handle. I should mention though that there are less used ones that I haven't put in and some that are impossible to support because QIF doesn't even have syntax for them.

#2 As I said, you have to map all the different terms, what might seem obvious to you as a human isn't going to cut it. For instance, bought, sold. Note that for the real common ones like Buy/Bought you would want to select the option for using it for all securities, but there are cases where sometimes it is different for different securities. This is one of the main complications of importing investment transactions from CSV files. The user has to understand what they want/need for a given security action/security so that it is "translated" to the right security action in Quicken.

#3 Clearly stated in the documentation, the name you give in ImportQIF for QIF output has to match exactly what is in Quicken. What's more Quicken's QIF import has a limit on the number of characters that account name can be. I forget what that is, something that like 13 or 14 characters. This is all a Quicken thing. Note that if you use an account column/field then you would this would be the name that has to match. EDIT: In QFX mode, Quicken uses the "account information" data only as a unique identifier, that it then maps to an account in Quicken. As such, on the first import of an account with that unique information Quicken will ask if it should create the account or link to an existing one. The actual account name in the QFX file isn't used, unlike it is when importing a QIF file.

#4 QIF imports work by security names. QFX imports work by cusip Ids. Again this is just how Quicken works.

#5 See #4, also watch out for the fact that you might not have all the transactions from the start of the account and as such Quicken might create placeholders for what is missing.

One more thing. Don't try to use Simply investing mode. It will not work. For it to work one has to know what the current balance is of all the shares and cash. Something that ImportQIF can't know.

FTERoo

I just worked through an IRA ROTH account going back 10 years, 900+ total transactions throughout that time period. @Chris_QPW, your quickenperlwiz tool works great.

You DO have to go step by step to check the result. This MAY have been a function of my process. Downloads were only available for the latest 12mos. For the other 9 years, I had to use EOY Statements in PDF form, convert them to Excel, and import the result. PDF conversions alone are imperfect, so there was a lot of "hand coding" within the initial Excel output to adjust formats, data positons, and the needed mapping to get it to work.

I've done A LOT of this in my career, so it's all familiar to me. That familiaity was in many ways a motifvation to see if I could do this. I had begun by inputting the transactions manually off the monthly paper statements in my files.

All in all, VERY preferable to inputting 900+ transactions individually, and I got faster with each year's data.

Great tool, esult ties out to the statements, and I know I can pull in other data should I ever need to!