How does Generic Import know what transactions to skip

[ThomasK]

Hi Mark,

I see that in FM v2016 you have the Bug Fix so that imports are skipped if they are already in Fund Manager. After reading historic posts I still have a few questions as I need to determine what csv output I want to use or if I need to manage that in some way. This might be the same answer for quite a few of us Canadians and those of us who trade frequently.

Up here in Canada none of the banks provide OFX format downloads but TD allows an export of account history line items as per the statement, so manual entry is avoidable. Also, there is an "active trader platform" that allows for data export of transactions and holdings to a csv file. This platform by the way is the same for many banks and brokerages, just the logos differ.

I am leaning to use the Active Trader output as I can do it during the day instead of waiting for WebBroker to post the transaction and the WB output includes some line items I prefer to deal with on a monthly basis, rather than each and every one.

BUT, either way the csv output shows every fill...so that if I by 300 shares of XYZ inc. it might have been filled in 100 share lots so I'll have 3 lines...same date, same symbol, same quantity.

Question #1: How does the Generic Import function "know" what transactions are duplicates and thus skip importing them?

Question #2: Do I have to 'add' some data to the output so FM knows that it is a unique line? What do you suggest?

Thanks,
Thomas

[Mark]

Hi Thomas,

Since you will have all of these 3 transactions in the same CSV file, it knows they aren't duplicates. It only is considered a duplicate if the exact same transaction is already in a Fund Manager investment and in the CSV file too. It doesn't compare CSV entry to CSV entry, those are assumed all unique.

The CSV import is quite powerful, but if your broker offers either a QFX, OFX, or QIF formatted file, those are easier to import. They might call them something different, like a Quicken file, or a Money file, but you can tell by the extension of the file as it downloads.

[ThomasK]

Thanks Mark.

I did some trial downloads. My Bank/Visa downloads as OFX, QFX, CSV. My Broker only downloads as CSV. As I'm pondering some other bookkeeping issues using Quicken I've come across the ability to convert from CSV files to QFX (new Quicken Format). [Here's one that has come up as good: https://www.moneythumb.com/convert-to-quicken/. The firm does a whack of other financial converters, including pdfs to usable file formats. The free trial is fully functional just limited to the first 10 transactions for conversion. Seems like something I (or you) could test. There are CSV to OFX converters also.]

So, with such a converter, there are 2 options to import trading data into Fund Mangaer:
1. Stick with the CSV download from my bank then use the Generic Import function into Fund Manager
2. Convert the CSV data into QFX and then import into Fund Manager
3. Convert the CSV data into OFX and then import into Fund Manager

So, the question now becomes.."Would converting from CSV into QFX (or OFX) just be easier or 'better' in some way? Less grief? Or is it beneficial to view the data in Quicken before importing into Fund Manager??"

Thanks,
Thomas

[Mark]

Hi Thomas,

There is no reason to view it in Quicken before importing into Fund Manager. The answer to your other question I guess depends on how easy/accurate the conversion goes. Fund Manager can import OFX/QFX without you doing anything, but the conversion may not be real smooth from a CSV. The CSV can be imported using our Generic import, but you may have to massage the data, and you have to specify the format string during import. You may have to do something similar to get it through a conversion tool, so I'd probably lean towards going directly into Fund Manager, but you can experiment, and decide for yourself.

[ThomasK]

Thanks Mark,

I'm going forwards with Generic Transaction Import of my csv's. Below are a series of questions to supplement the help files and clarify my understanding. I'm confident that with this in place I can semi-automate my data entry as the Canadian Banks up here don't export well.

Background:
I use something called Active Trader Platform (ATP: third party software that most brokerages private label) that allows for Export to a csv from the "Executions" tab. Each Account in ATP has an Executions tab, so for the handful of accounts I'm trading in ATP, I need to export the transactions for EACH account. I usually export at the end of a trading day but that in of itself usually INCLUDES data from the previous few days.

Q1. My understanding is that v2016 will ignore the duplicates as each line imported has a specific DATE.

Q2. Do you think it is best to import One-Big-File? I would of course massage the data in excel prior to importing so that each line was "complete' on its own.

Q3. For the date, my csv has it as an excel number (ie what shows as "2016-02-26 15:23" really is equal to 42426.64113) BUT I looked at one of your examples in the forum and the csv is displaying a date as "2002-02-16" but that too pastes as a number, in this case 37303....
(a) am I just trusting that the export to csv and importing takes care of that automatically?
(b) do I need to truncate the decimals as my exporting seems to include them?
(c) or must I do manipulate in excel the date as exported to a TEXT or something like that?

Q4. Using ACCT is better than PORT it seems...much more precise.

Thanks,
ThomasK

[Mark]

Hi ThomasK,

Q1) Yes, Fund Manager will skip duplicates. It determines these by the date/value/shares/price/commission. If any number is different, then it isn't considered a duplicate.

Q2) It doesn't matter, whatever is most convenient for you.

Q3) This sounds like a formatting issue in Excel. The cell may be formatted as a number. You might try viewing the CSV file in a text editor, like Notepad. You can also format a cell in Excel, and tell it the cell is text so it doesn't try to calculate it, and show it as a number.

Q4) Agreed.