Importing Data

[chris319]

Hi Mark -

By chance I found the 64-page Quick-Start Guide for Fund Manager:

https://www.fundmanagersoftware.com/manuals/fm_printable.pdf

It would have been extraordinarily helpful, and would have saved us both a lot of time and effort, if the Quick-Start Guide had been attached to the email containing my license key, together with a link to this document. It would require no cost or effort to attach this file to the email containing the license key and to include links to the document.

I am preparing to import about 400 transactions from a .csv file into Fund Manager. This is too many transactions to enter manually. Right away a question arises.

The documentation calls for an ANUM of "price" for buy and sell transactions. My question is, does "price" refer to the unit (per share) price or the overall cost of the transaction (shares x unit price)? I want to clarify this point before continuing with the preparation of the .csv file for import.

For all transactions prior to my brokerage charging zero commissions, the commission is baked into the total cost (unit price x shares + commission) of the transaction. It would take a lot of gravedigging to determine which transactions were charged a commission and "unbake" it from the total cost.

Also, what is the preferred transaction code for cash deposits and withdrawals?

Thank you for your help.

[Mark]

Hi chris319,

Okay, thanks for the feedback/idea on the quick-start guide.

For the generic transaction import, ANUM is the price per share when talking about BUY / SELL transaction codes. BNUM is the number of shares, and CNUM is the commission paid. As an example, if you bought 100 shares for $15/share, with a commission of $7 for a total of $1507, the fields would be:

ANUM: 15
BNUM: 100
CNUM: 7
TRAN: BUY

For cash deposits/withdrawals I would suggest using a BUY/SELL. You could use transfers, but BUY/SELL are simpler, and accomplish the same thing. There is no need for a separate cost basis date/value, which are the main benefit of transfers.

[chris319]

Hi Mark -

After carefully preparing a csv transaction file for import, when trying to import it I'm only seeing 308 out of the full 404 transactions in the csv file.

I'm not seeing any of my cash deposits (purchases) or withdrawals (sales). I made up the ticker symbol CASHBAL because "CASH" is the actual ticker symbol of Meta Financial Group, Inc.

I wiped out my previous portfolio and am starting afresh with the imported transactions. I have designated CASHBAL as the default cash account for the portfolio.

What's going on?

Thanks,

Chris

[chris319]

OK, I figured it out.

When preparing the csv file in Libre Office, for transactions not involving a commission such as splits and distributions, the spreadsheet cell for commission was left empty. In the csv file this appeared as two commas with nothing in between:

,,

This caused Fund Manager to skip the transaction entirely. I went through and replaced ,, with ,0, filling in the blank field with zero.

Having done that, Fund Manager was still seeing only 403 of my 404 transactions, so I had to trick it by adding a trick transaction to the bottom of the csv file. This didn't work at first because I had zero as the transaction amount which caused FM to skip the transaction altogether. The root cause seems to have been that FM counts one fewer transaction than are actually present in the csv file, a definite bug.

I think it would be advantageous if in csv files a blank filed ,, is interpreted as zero ,0, and the transaction not ignored.

Thank you.

[chris319]

Another snag: FM recognizes that CASHBAL is the default cash account for the portfolio. Thus, it does not try to fetch a price on line for CASHBAL.

I had to create a phantom security for margin interest, MARG INT. The problem is that FM thinks this is a valid ticker symbol and tries to fetch a quote for MARG INT from the quote server. Not a showstopper, though.

[chris319]

In the CASHBAL data register, there are a bunch of phantom transactions whenever I buy or sell another security. Is there any way to inhibit FM from creating these phantom CASHBAL transactions, or am I better off manually deleting them?

[Mark]

Hi chris319,

You shouldn't need to add a dummy transaction to get the last line imported. I haven't been able to reproduce any bug like this. I've attached a sample file I tested this with. It has 2 lines, and both are imported (use the format string: ANUM,BNUM,CNUM,TRAN,MM/DD/YY,SYMB)

If you have a problematic import file you'd like me to look at, please email it to me.

Investments in FM have a property "Investment property / Internet Retrieve / Retrieve Price", which controls whether or not an investment is retrieved when you retrieve prices. By default, this is turned off for "Cash" type investments. You can set the default of this option for any investment type. If you want to stop something from retrieving, just turn off this property for that investment.

Since you designated CASHBAL as your default cash account, it will receive all the corresponding entries for the default cash account. If you don't want these, either set your default cash account to "<none>" or when importing, turn off the option at the bottom of the "Preview Importing Transactions" dialog box that says "Automatically Record Corresponding Entries in Default Cash".

[chris319]

Hi Mark -

I drilled down deeper and found the following:

I checked for any blank fields in the csv file using Libre Office and there were none.

FM is indeed importing the first and last transactions in my file and all transactions in between. However, it still reports only 403 of the 404 transactions. I'm thinking maybe the import window with the list of checkable transactions to be imported is misreporting the number of transactions above the pane where the list of checkable transactions appears, despite the fact that those transactions are actually being imported.

I closed all investments and reimported them with the default cash account disabled and this fixed the problem with phantom transactions.

So far I appear to be in good shape, but there is still the lingering problem of the number of transactions to be imported being erroneously reported.

I also configured FM to not fetch prices on line for CASHBAL and MARG INT.

Thank you for your help.

[Mark]

Hi chris319,

That number should be accurate. It sounds like some transaction in the middle of your file is being skipped. We should add some log file to help you find this, but that currently isn't available. You might try saving just the first half of the file to a new file, import that, and see if you're still missing the transaction. Repeat this to narrow down which transaction is being skipped.

[chris319]

That number should be accurate. It sounds like some transaction in the middle of your file is being skipped. We should add some log file to help you find this, but that currently isn't available. You might try saving just the first half of the file to a new file, import that, and see if you're still missing the transaction. Repeat this to narrow down which transaction is being skipped.

With 404 transactions, this is asking way too much. It assumes that a transaction is being arbitrarily skipped. As I said, I wrote code in Libre Office specifically to detect blank fields and there were none. Here is the Libre Office code:

Code: Select all

=IF(A1="","WARNING","")

It was used to inspect all 6 columns and was pasted into all 404 rows.

In addition, I'm having to go through my brokerage data to manually add SEC fees, which is not a problem. However, I've spent the last half hour struggling to reconcile one transaction with my brokerage data and the transaction entry wizard is fighting me every step of the way. Try as I might I can't get the transaction total right no matter in what sequence I enter the total, the share price, the share amount and commission. The security involved was subsequently split and this affects the share price.

For now I'm giving up on Fund Manager, though I may return to it later having paid $295 for it.

[Mark]

Hi chris319,

I understand it can be hard to get started. If you'd like to figure it out, give me a call at 480-705-0129 and we can work through this together.

[chris319]

Hi chris319,

I understand it can be hard to get started. If you'd like to figure it out, give me a call at xxxx and we can work through this together.

Thanks for the kind offer, Mark. Maybe at a future time.

Chris

[chris319]

OK Mark, this time I have good news!

It was laborious, but I now have all transactions imported and everything seems to balance out.

One issue: in a custom report I still have a stock appearing which is no longer an open position, i.e. FM knows there are 0 shares but it still appears in the report.

One more suggestion: On the "Investment Goal" list, please add <uncategorized> as one of the goals.

Thank you for all your help and support.

Chris

[Mark]

Hi Chris,

Excellent.

On the 0 share investment, you can "hide" that. You can either hide investments individually under "Investment Properties... / Other / Hidden", or you can do it in bulk with "File / File Operations / Hide Sold Investments...". For much more on this, see the Tutorial on Hiding Investments.

All of the category names are fully customizable. See "Options / Category Labels...". For a tutorial on using Categories, please see the Tutorial on Categories.

[chris319]

you can do it in bulk with "File / File Operations / Hide Sold Investments...".

I did that and I'm still seeing closed positions in the Portfolio Performance report.

On the "Investment Goal" list, please add <uncategorized> as one of the goals.

I'm suggesting that this be added as a program default. You already have it under Asset Type.