a Generic Transaction Import based port. management design

[modelerMike]

Good day FM professional
A question regarding a Generic Transaction Import based portfolio management design.

After having assembled, organized and cross-referenced Generic Transaction Import KEY data
with ‘Investment Properties Dialog’ data my inclination is to organize
my portfolios in the following way:

First use Generic Transaction Import to load an ‘Indicatives’ portfolio
where the Indicatives KEYS are:
SYMB, NAME, CURRENCY, ITYPE, CUSIP, IATAXFREE, IAMETH, ASSET, GOAL

using the following format;
SYMB , NAME, CURRENCY, ITYPE, CUSIP, IATAXFREE, IAMETH, ASSET, GOAL, TRAN ED ANUM BNUM CNUM
( a one share buy transaction for all ‘indicative portfolio’ entries with a price for all entries of 0.001 & load into Indicatives portfolio)

This ‘Indicatives’ portfolio load would include all investments in all actual portfolios.

The reason for creating this ‘indicatives’ portfolio would be to use it as a central data repository
for all actual portfolios’ investment indicative data;
that is the ONLY portfolio that would be used to change
investment properties in ANY actual portfolio would be done in the ‘indicatives’ portfolio thus
making investment changes simpler to manage.

Then load actual portfolios with transactions of the form;
PORT, SYMB, ED, TRAN, ANUM, BNUM, CNUM
and load it such a way that it would not allow these loads to create any new investments.


The point here is to isolate my actual portfolios from all the ‘inaccuracies’ I have encountered
trying to RETRIEVE portfolio transactions and have complete control over ALL portfolio content
and pricing.

Yes it would be nice to automatically RETRIEVE prices for all portfolios whenever I want at the
touch of a button and it would be nice to use RETRIEVE to update transactions once the
portfolios are created BUT I simply have no confidence in the integrity of the brokerage accounts
to do this correctly 100% of the time. The headaches fixing some botched RETRIEVAL strikes as much nastier to investigate and resolve than simply doing all the grunt work myself to insure data integrity.

Am I missing something ? What do you think ? Guidance/comments appreciated.


Regards,

Mike

[Mark]

Hello Mike,

and load it such a way that it would not allow these loads to create any new investments.

If you make this restriction, how will any new investments for your actual accounts get created? Each account that holds some shares of an investment should have its own investment. Maybe you only have one account, and you want to update your existing "indicatives" investments? If so, you will have an incorrect share balance, by the 1 fake share.

Where is the data coming from to do this generic import? Is it also coming from the broker? Is it more reliable for some reason? Is it the transaction details you are concerned about not being reliable, or the investment not getting set up with the right type, accounting method, etc...? If you are using the Advisor version you can use "Edit / Symbol List..." and make property changes to all investments with the same symbol at once.

[modelerMike]

Thanks for the feedback Mark; sounds like my idea of an ‘indicatives’ portfolio does not make sense, good to know before I tried to create one.

I am just starting out with your product and want to understand how all the data inter- relate before getting up to speed with the FM Report and Graph functionality that looks to be very valuable. It sounds like all data are portfolio specific; got it.

I am using the Professional Version and i have at present RETRIEVED
4 accounts from 2 different brokers.

I need to go back to the portfolios i RETRIEVED and dig into the details to find out precisely why they are missing beginning positions and/or fail to correctly reflect
non-cash transactions and associated cash account transactions.
I also need to get up to speed with FM’s Reconciliation functionality to help me
get my portfolio values making sense over time.

I have not yet done any Generic Transaction Import loads; i am simply trying to think
through an approach before proceeding in an attempt to ‘measure twice cut once’ .

Bottom line from what you said is that if i go the Generic Transaction Import file load
route ; i should simply create all my portfolios and leave it at that.

Regards,

Mike

[Mark]

Hi Mike,

Glad that helped. One issue that causes confusion sometimes is the concept of default cash accounts. If you are not seeing a matching cash transaction for every stock buy/sell/dividend, then you may need to make sure your sub-portfolio has a default cash account assigned, and that when you retrieve, you turn on the option to "Automatically Record Corresponding Entries In Default Cash Account". This option is on the bottom of the transaction preview dialog when you retrieve. To read about default cash accounts, see:

http://www.fundmanagersoftware.com/help ... _cash.html

[modelerMike]

Thank you Mark.
I will be mindful of the default cash.

Regards
Mike

[modelerMike]

Generic Transaction Import file load update;

Got Generic Transaction Import file load working.
Validated imported transactions with resulting portfolio’s exported transactions.
Everything looked good, the splits worked perfectly.
Cash distributions were treated simply as REINVEST transactions as per your Message Board guidance.

I got messed up when I did not follow your instructions about transforming Excel sheet data into a comma delimited .csv file before
copy/pasting the file load data into a .txt file via notepad.

Here is what got me a successful load;
take .xls; format single worksheet the way you want; without separating columns of spaces
save it as a .cvs file
cut/paste csv file into notepad to make a .txt. file
then load the .txt file with the format string :

SYMB CUSIP ITYPE MM/DD/YY TRAN ANUM BNUM CNUM NAME

sample data from .txt. load file ( with #s changed but representative of the format/approach )
I plan too update ITYPE entries later. What would you suggest as the best way to do this ?

FCASH 999999999 Cash 4/9/2007 TXIN 1 10000 10000 CASH
DJP 06738C778 Stock 4/10/2007 BUY 51.5162 5 10.95 BARCLAYS BK PLC IPATH INDEX LKD SECS TO
DVY 464287168 Stock 4/10/2007 BUY 72.41 8 10.95 ISHARES TR DOW JONES SELECT DIVID INDEX

before this successful load, I had lots of problems when the NAME key was not on the end ......
that is I tried a test load in the following format;

SYMB CUSIP NAME ITYPE MM/DD/YY TRAN ANUM BNUM CNUM

FCASH 999999999 CASH Cash 4/9/2007 TXIN 1 10000 10000
DJP 06738C778 BARCLAYS BK PLC IPATH INDEX LKD SECS TO Stock 4/10/2007 BUY 51.5162 5 10.95
DVY 464287168 ISHARES TR DOW JONES SELECT DIVID INDEX Stock 4/10/2007 BUY 72.41 8 10.95

and got strange results; my test file had 13 entries but only three loaded AND TWO HAD BOGUS DATES !!! ????
The 3 transaction imported were the
one transaction set the imported date = July 10, 2007 not 4/10/2007 in the input file
another transaction date = Jan 10, 2007 not 4/10/2007 in the input file.
the third transaction imported , the TXIN transaction looked OK.

What concerns me here is if I ever want to do a Generic Transaction Import and have more than one KEY whose data
are character strings with spaces in them ; I suspect I may have problems.

In spite of the bumps in the road getting up to speed with Generic Transaction Import load functionality,
I am glad I bought FM.
I see it being valuable in both helping me manage my exiting investments and doing research.

Regards,
Mike

[Mark]

Hi Mike,

Glad you made it work out.

On the "Name" problem... You cannot import a field that contains the same character that you are using as a delimiter. You are using spaces as the delimiters (field separators) in your format string:

SYMB CUSIP NAME ITYPE MM/DD/YY TRAN ANUM BNUM CNUM

Therefore, none of your fields should have spaces in them, or else Fund Manager will terminate that field at that space. Instead, create other delimiters, like double quotes:

SYMB CUSIP "NAME" ITYPE MM/DD/YY TRAN ANUM BNUM CNUM

and then put those same delimiters in your importing data:

FCASH 999999999 "CASH" Cash 4/9/2007 TXIN 1 10000 10000
DJP 06738C778 "BARCLAYS BK PLC IPATH INDEX LKD SECS TO" Stock 4/10/2007 BUY 51.5162 5 10.95
DVY 464287168 "ISHARES TR DOW JONES SELECT DIVID INDEX" Stock 4/10/2007 BUY 72.41 8 10.95

Now, Fund Manager won't stop reading in the NAME field until it hits that closing double quote character. All spaces within the name are included in the name field.

This is quite common to do, especially if you save a CSV file directly from Excel. A CSV file uses commas as the delimiters. Any field that actually contains a comma will have double quotes put around it, so you know that comma is part of the field, and not a delimiter.

On the ITYPE question... You should include this in your initial import. This field is only utilized when creating new investments. You cannot import this information afterwards into existing investments. If you import without it, that is fine, but you will have to change the investment type manually via the "Investment Properties..." dialog if the default is incorrect.

[modelerMike]

Good to know.

THANK YOU Mark

[modelerMike]

Good day FM professional,

If one wants to use FM to record/retain transaction pricing for buys , sells, reinvested dividends
it seems that one must avoid doing PRICING RETRIEVALS which would overwrite
the transaction pricing with pricing as of close.

If this is the case then one needs two sets of portfolios,
one created with transaction pricing for assessing tax cost basis and OOP cost basis.
This set of discrete time valuation ‘cost basis’ portfolios will not have
PRICING RETRIEVALS done on it.

Then these transaction priced portfolios could be copied as another set of portfolios that can have
PRICING RETRIEVALS done in order to get smooth day-to-day continuous portfolio and investment value tracking functionality and performance analysis over time based on closing prices.

I think these two sets of portfolios would have to be maintained in separate directories to keep their data independent.

Does this approach make sense in order to retain the ability to have both access to cost-basis
data and yet also be able to track day-to-day valuation and performance reporting driving from closing pricing. ?


Regards,

Mike

[Mark]

Hi Mike,

This is not necessary. Retrieving prices does not change your recorded transaction prices. Transaction prices are independent of closing prices. For example, take a look in the Data Register. When you select the "Prices" data type, you are viewing all the recorded closing prices, which you can get when you retrieve prices. If you switch to the "Investments (all)" data type, this lists all your transactions. Each transaction keeps its price recorded, and is independent of closing prices. Transaction prices are used to determine your cost basis, not closing prices.

[modelerMike]

Good day Mark,

Thank you for the guidance;
I missed your point in the documentation.
That makes life easier !

Mike