Is there a way to import Scotia iTrade transaction into Quicken Deluxe Canada?

Miguel

I cannot find a simple way.

Thanks.

Quicken Kristina

Hello @Miguel,

To assist with this, please provide more information. What is the login URL (web address) you use when logging into your account(s) directly through the financial institution website? Have you checked the financial institution website to see if they allow you to download .QFX format files? If they do offer that file format, then you should be able to download the .QFX file from the financial institution and import it into your Quicken. Please see this article on setting up a web connect account for more information.

I look forward to your reply!

JosefW

Hi @Miguel,

I believe the short answer is maybe, but not without jumping through some technical hoops and using another program to convert the file from CSV to QFX or QIF.

This is an old limitation for pretty well all Canadian Investment accounts in Quicken Windows Canada that many users are wishing would be fixed. The fact is that it's the Institution that is responsible for making it possible to do so and so far they have been unwilling to do so. I recommend calling Scotia iTrade to request that they enable this feature.

I have had investing accounts at Scotia iTrade and at TD Direct Investing for many years and used Quicken to track investments. I have found no direct way to import investment transactions into Quicken. You can only download a CSV file of investment transactions from either brokerage and then find a way to convert the CSV file into a QFX or QIF file and then import that file into Quicken.

There are programs that will do that and one that is featured in Quicken forums on this topic. I have never tried it myself so I don't know how well they work or how easy they are to use.

To download a CSV file of investment transactions from Scotia iTrade select the Transaction History tab, select the range of dates and then at the top there will be a download symbol. Click this and a window opens with details on the .CSV download. Click the link with the number of transactions at the bottom to start the download.

One note of caution; set up a new or duplicate account that you can experiment with and use that before proceeding.

Here are 2 links to these forum conversations and a link to the program.

http://www.quicknperlwiz.com/

https://community.quicken.com/discussion/7944171/importing-investment-transaction

https://community.quicken.com/discussion/7885902/how-to-convert-csv-file-to-qfx-file

If you do try this, let us know how it works.

JosefW

@Arctic Hare @JoelC @Miguel @LostCanuck

Re: Importing Investment transactions into Quicken Canada for Windows vR54.17

BTW, thanks everyone for the information provided in the "Importing Investment Transaction" Post above.

Out of curiosity, I decided to try the ImportQIF program developed by @Chris_QPW.
I used it for 1 account at TD Direct investing. One limitation with downloading investment transactions at TD is that they limit the transactions to the most recent 6 weeks or so with no way of specifying which dates or types of transactions. While both allow CSV downloads of investment transactions, Scotia iTrade has the advantage of being able to specify far greater date ranges and also specify transaction types.

The instructional documentation for ImportQIF is extensive. It is also nuanced and complicated depending among other things on which older version of Quicken is used. One problem is that each financial institution has its own way for setting up the column headings for the transactions. These then have to be mapped to match the headings that Quicken recognizes and uses.

I was able to get it to work for one import of 17 transactions into one account from TD, but not without some modifications of the original CSV file to allow Quicken to match the headings. While I haven't taken it any farther than this, I believe that once a workable mapping for an account has been developed that setting can be saved.

But I think it would also require that for each CSV download, one would have to modify the file, insert any missing headings and manually fill each transaction with that information, and then save that CSV file before importing it into Quicken as a QIF.

With TD and with Scotia they both have headings titled "Description" in the CSV which will include a lot of information that will have to be broken out and entered into the missing columns, e.g. the Security Name, and if it's a Dividend Reinvestment, also the total value of the DRIP. In the case of TD the price will have to be manually ascertained by dividing the total dividend amount by the number of shares, (which are shown in a separate column), in order to establish the price of the DRIP. This will then have to be manually entered into the Price column for each DRIP transaction before saving that CSV file prior to importation.

For the last thirty years I have been manually entering investment transactions. Quicken has a great feature that allows specific transactions to be memorized and reused from a selectable list, which makes the process faster and easier. To be honest I think I will stick with this method for now.

Bottom line, it would so much easier if Canadian banks could make the effort to provide this service for its investment customers as it does for banking.

JoelC

@JosefW :

1. I appreciate the update. I looked at @Chris_QPW created and I decided it was too much for me though kudos to him for developing it.

2. In respect of manipulating the CSV file this can easily be automated. Excel includes a feature called Power Query that is relative easy to learn and use (i.e., it is point and click though it can be scripted for those who want to dig deep). I use it extensively to automate the re-formatting of downloaded for data import / use in Excel.

3. In terms of entering transactions I am with you in that a) I enter them manually (thankfully I am not a day trader) and b) I wish teh banks would make them downloadable.

Thanks.

mshiggins

In addition to the very useful "ImportQIF" utility, Quicken users can use an alternate method to convert .CSV files to QIF files. Whether the alternative is "better" will likely depend on the user.

The alternate method is called "XL2QIF": it is an Excel Add-in and can be downloaded here: http://xl2qif.chez-alice.fr/xl2qif_en.php

Once XL2QIF had been installed in Excel: it can process any properly formatted Quicken data in Excel from any source, including .CSV files.

The user imports a .CSV file of Quicken transactions into Excel, starts the XL2QIF Add-in and tells XL2QIF which columns contain which Quicken fields.

[It seems best to delete any columns that contain data not desired in the QIF file.]

Here's a snapshot of the XL2QIF dialog for converting an Excel file of Quicken investment transactions to a QIF file.

XL2QIF can also do the reverse and convert a QIF file into an Excel file (which in turn allows the user to convert QIF file data into pretty much any file type that Excel can create).]

I have used XL2QIF several times over the years so I can probably provide guidance, if needed. But I know nothing about Quicken Canada, though I think that's not likely to be an issue for this situation.

-JP

Chris_QPW

@mshiggins XL2QIF generates compile errors on my Windows 11/Office 365 Excel so I can't check it out, but I'm curious of how it would handle the problem stated by @JosefW where there isn't a column for the security, only a mention of it in the description column? ImportQIF currently doesn't have a good solution for this one mostly because this is the first time I have heard of it and really wondering if it is worth putting in a change for this. One can do a Mapping of the description to security name, but it isn't a wild card mapping, so I doubt it is that useful. It certainly wouldn't work for "Bought 3434 shares of XYZ". Did run into the same problem for a missing action column and so the Map Actions is a wild card kind of mapping. But if both the security and the action columns are missing and both would have to draw from the same description then that would definitely add even more complexity to the problem.

Another thing I'm curious about is how it handles the fact that there isn't any kind of standardization on the action terms, not to mention the "reuse of terms". For example: Buy, Bought, Purchased, Distributed, Received, …

Converting banking transactions is relatively easy in comparison to investment transactions with all the extra information that has to be mapped to the right actions and due to the fact that the reports provided by the financial institutions were never intended for anything but human consumption.

mshiggins

First let me say that I didn't mean to imply that XL2QIF could - on its own - do a better job than ImportQIF. If I left that impression, I apologize.

And clearly, if the .CSV file contains incorrect or missing data, XL2QIF wouldn't be able to help ... without assistance. But, that "assistance" could come from Excel.

[In fact, as I see it, .CSV files are not generally good sources of data for

Quicken - especially (as already noted in this discussion) for investment data. In most of my tests - using U.S. investment financial institutions; much cleanup work had to be done for XL2QIF to create a valid QIF file.]

On the plus side, Excel has some built in features that might be used to "reformat" the Excel data after importing the .CSV file. I'm not a sophisticated Excel user, but I believe Excel could improve certain situations where the .CSV file data was not usable as downloaded.

If data (such as security name) is imbedded with other data (I did not see that with my U.S. financial institutions), it seems the devil would be in details. 

In some situations, those details might allow extracting the desired data and placing it another Excel column (or removing the extraneous data). 

But, in my tests, even when the security name was in its own Excel column, it virtually never had the same  name as the name I used in Quicken - so all the security names would have had to be modified, before creating the QIF file. In that case, the Excel data could be sorted on security name and multiple rows of the same security could have their names changed fairly simply.

Along the same lines, if the Action value were "Purchased" in the .CSV file, that could be converted to "Buy" using Excel.

Finally, I suspect that at least some of the modifications needed could be  recorded in an Excel macro, allowing the user to make fairly easy work of  "conversions" after the first one.

[I can't even attempt to address the Windows 11 install failure; I only have one pc with Windows 11 and it doesn't have Excel. I tried to contact the developer, but could not get the link to work.]

-JP

Chris_QPW

Thanks JP.

OK, that makes more sense. I was wonder if there was some "magic" that I was missing.