Generic Transaction Import Dialog

This dialog is available from the File / Import / Transactions / Generic... menu command.

Press the "Next" button after filling in all of the above fields. The importing file will be read in, and you will be taken to the Preview Importing Transactions page to see what transactions will be imported.

This command can import transactions from a very wide variety of text files, with differing formats. You specify the format of the import file at the time of importing. You will also specify the complete path and filename of the file to be imported. You may choose to import all the transactions into a specific investment, or you can make use of the SYMB, NAME, and PORT keys to import the transactions into a variety of investments/sub-portfolios.

The specified format string is matched against each line in the importing file, one line at a time. If the format string contains the required information and the imported line matches this format string a single transaction will be imported for each matching line in the importing file. In order to import a transaction each line must contain at least the following information:

If the investment to import transactions into is not "specified" then each line must also contain the symbol of the investment which should be updated. There may be more information on each line, but only these are required.

You specify the input format (called "format string") of the file to be imported. Any character may be part of the format string. Fund Manager recognizes certain character combinations (called "keys") in the format string to represent various data:

KEY	VARIABLE	Comments
SYMB	symbol	To indicate which investment(s) should be updated with the transaction data you must either "specify" an investment or use the SYMB key.
MM	month	Use 1 method of specifying the date. Use any one (and only one) of these: MM, DD, and YY UD ED The YY key can represent either a 2 or 4 digit year. The UD key represents the presence of a 6 character string containing the year, month, and day. The string has the format: yymmdd, where yy = year, mm = month, and dd = day. The string must be the full 6 characters long. The ED key is similar to the UD key, but matches a full 8 characters in the format of yyyymmdd.
DD	day
YY	year
UD	year, month, and day (YYMMDD)
ED	year, month, and day (YYYYMMDD)
TRAN	transaction type	see the TRAN value table below.
ANUM	transaction type dependent number	ANUM, BNUM, and CNUM meanings vary depending on TRAN value, see table below.
BNUM	transaction type dependent number
CNUM	transaction type dependent number

KEY	VARIABLE	Comments
NAME	investment name	Use this field when you want to specify the investment name of a new investment, or additionally qualify which investment is updated with the transaction data.
ITYPE	investment type	Specifies the investment type when creating a new investment. Can be any string matching the list of investment types (Stock, No-Load Mutual Fund, Loaded Mutual Fund, Bond, Cash, Index, Option, Exchange Rate, Exchange Traded Fund, Treasury Bill, Annuity, Future, or Other).
PORT	sub-portfolio name	Use the PORT key to control which sub-portfolio the associated transaction will be imported into. The PORT key is optional, and when used will override the destination portfolio selection on the import dialog. This field will be used to to match or create a new sub-portfolio by name (not account number).
XX	unused	A wild card key can be used in the format string to identify fields in the imported line that do not get used. This key is analogous to the wild card character '*' in DOS. The XX key will match everything up to the delimiter immediately following this key. If no delimiter is specified after the last XX in the format string it will match the rest of the line.
!REM	comments out the remainder of the format string	This is a comment key that can be used to add comments at the end of the format string. Fund Manager remembers the last 6 used format strings. Comments can be useful to remember the source of transactions for the associated format string. Everything in the format string after the comment key is considered a comment and is ignored by Fund Manager when importing.
MEMO	transaction note	Use the MEMO key to map some text into the memo field of a transaction.
TAB	tab	A single space in the format string will match any amount of white space (a single space, multiple spaces, or tabs) in the imported line. A tab key can be used when importing tab delimited files. Without using the tab key all white space in both the format string and imported line will be treated as a single space. When files contain both spaces and tabs and it is necessary to differentiate between them, use the TAB key. The TAB key is unique in that it can be adjacent to any other key without delimiters between the TAB key and any other key.

Each of the keys (except TAB and XX) can only be specified once in the format string.

The TRAN key represents a character string present in the imported line. The TRAN values Fund Manager will recognize are shown in the table below.

(The key TRAN is what is placed in the format string. The TRAN values such as BUY or SELL need to be present in the imported line.) The TRAN key does not have to be specified in the format string. In this case all transactions are assumed to be purchases.

Every format string must have all three ANUM, BNUM, and CNUM keys present. The keys must be present in the format string, and some associated number must be present in the imported line, even if it is not being used for the particular transaction type. The values represented by ANUM, BNUM, and CNUM are a function of the type of transaction currently being imported. The values represented by these keys can change with the transaction type for each line being imported. The values represented are shown in the following table:

TRAN value	ANUM	BNUM	CNUM	Transaction Description
BUY	price	shares	commission	purchase
SELL	price	shares	commission	redemption
DIST_D	value	(not used)	(not used)	distributed dividend
DIST_S	value	(not used)	(not used)	distributed short term capital gain
DIST_M	value	(not used)	(not used)	distributed medium term capital gain
DIST_L	value	(not used)	(not used)	distributed long term capital gain
DIST_I	value	(not used)	(not used)	distributed interest income
DIST_F	value	(not used)	(not used)	distributed account fee (out of pocket)
DIST_R	value	(not used)	(not used)	distributed return of capital
DIST_O	value	(not used)	(not used)	distributed other type of distribution
REINV_D	price	shares	commission	reinvested dividend distribution
REINV_S	price	shares	commission	reinvested short term capital gain distribution
REINV_M	price	shares	commission	reinvested medium term capital gain distribution
REINV_L	price	shares	commission	reinvested long term capital gain distribution
REINV_I	price	shares	commission	reinvested interest income distribution
REINV_F	price	shares	commission	reinvested account fee distribution (redeemed shares)
REINV_R	price	shares	commission	reinvested return of capital
REINV_O	price	shares	commission	reinvested other type of distribution
SPLIT	ratio	(not used)	(not used)	split

The 'price' values are share prices. The 'shares' values are the number of shares involved in the transaction. The 'value' and 'commission' values are total dollar values. All values can be imported in either decimal or fractional format.

Each key in the format string must be separated by at least one character, space, comma, or any other delimiter. (The only exception is the TAB key.) For example the following format string is illegal:

It is illegal because there is nothing separating YY from MM or MM from DD. The format string for a file like this would be correctly represented with:

The following table lists several examples of acceptable format strings and what the imported line would actually look like in each case.

In the first example a purchase of 500 shares at 25 3/8 per share plus a commission of $33, for a total of $12,720.50 (500 * 25.375 + 33 = 12,720.50) would be imported on October 15, 1997. The second example is the same, but shown with a decimal share price. A fractional share price can be imported when the delimiter between fields is not a space. The third example imports a distributed dividend for $75.50 on October 15, 1997. Notice that even though CNUM and BNUM are not used with distributed dividend transactions they must still be present in the format string, and an associated number must be present in the imported line (0's in this example). Also, notice that the key XX was used to skip over an unused field present ('junk' in this example) in the imported line. The last example shows the use of a comment. This example would import a purchase of 200 shares at $ 27.25 per share, plus a commission of $33. There is no TRAN key specified in the format string, so the transaction is defaulted to a purchase.

The generic transaction import can easily be used in conjunction with data already existing in a spreadsheet or other similar format. Most spreadsheet programs allow data to be saved to a text file. (Excel can save to a comma separated text format (CSV) which is very convenient for importing.) Minor modifications to the data prior to saving to a text file, including the addition of the TRAN keys, can enable the data to be quickly transferred into Fund Manager.

File to Import	Select which file to import.
Import Format	See detailed instructions below.
Import Destination	Choose which portfolio to import transactions into. If your "Input Format" includes the PORT key this selection is ignored. If you want to force all transactions into a specific investment choose the "Import All Transactions Into Specified Investment" option and all the transactions in the importing file will be imported into that investment. In this case the SYMB and PORT keys in the "Input Format" are ignored.

FORMAT STRING	IMPORTED LINE
UD,SYMB,TRAN,ANUM,BNUM,CNUM	971015,INTC,BUY,25 3/8,500,33
UD,SYMB,TRAN,ANUM,BNUM,CNUM	971015,INTC,BUY,25.375,500,33
CNUM;BNUM;XX;ANUM;TRAN;MM/DD/YY,SYMB	0;0;junk;75.50;DIST_D;10/15/97,INTC
UD ANUM BNUM CNUM !REM my spreadsheet format	971015 27.25 200 33

See Also