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, ACCT, 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	Specifies the investment name when creating a new investment. See Notes on Matching Transactions with Investments
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).
ASSET	asset type (category name)	Specifies the asset type when creating a new investment. Can be any string matching a defined asset type category label.
GOAL	investment goal (category name)	Specifies the investment goal when creating a new investment. Can be any string matching a defined investment goal category label.
SECTOR	sector (category name)	Specifies the sector when creating a new investment. Can be any string matching a defined sector category label.
CURRENCY	currency	Specifies the currency when creating a new investment. Can be any string matching a defined currency symbol.
IACCT	investment account number	Specifies the investment's account number when creating a new investment
IDESC	investment description	Specifies the investment's description when creating a new investment
CUSIP	investment CUSIP	Specifies the investment's CUSIP when creating a new investment
XRATE	transaction specific exchange rate	Specifies the exchange rate for this transaction to the default currency. Leave blank or set to 0 to not specify a rate. Ignored when investment's currency is already in the default currency.
BONDMAT	bond maturity date	Specifies the maturity date for a bond when creating a new investment. Format is: mm/dd/yyyy
BONDCOUP	bond coupon rate	Specifies the coupon percentage rate for a bond when creating a new investment. Specify as a percentage (i.e. enter 3 for 3%). Specify both BONDMAT and BONDCOUP to assign "Bond Interest" as the income style when creating a new investment.
BONDFREQ	bond coupon payment frequency	Specifies the bond interest payment frequency when creating a new investment. Optional values include: 1 = Monthly 2 = Every other month 3 = Every three months 6 = Twice a year (default) 12 = Yearly mm/dd/yyyy = At maturity, and this date is the bond's issue date
BONDBAS	bond day count basis	Specifies the bond day count basis when creating a new investment. Optional values include: 1 = US (NASD) 30/360 (default) 2 = Actual/Actual 3 = Actual/360 4 = Actual/365 5 = European 30/360
CIP01 through CIP10	custom investment property 1 through 10	Specifies the investment's custom property when creating a new investment. When the custom property is a date the format is mm/dd/yyyy.
IAMETH	investment accounting method	Specifies the investment's accounting method when creating a new investment: 0 = First In First Out (default) 1 = Average 2 = Specific Lot
ITAXFREE	investment tax-free status	Specifies the investment's tax-free status when creating a new investment: 0 = Taxable 1 = Tax-Free 2 = Set based on sub-portfolio's tax-free property (default)
ACCT	sub-portfolio account number	Use the ACCT key to control which sub-portfolio the associated transaction will be imported into. The ACCT 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 account number. Both the PORT and ACCT keys can be specified, in which case the ACCT key takes priority (transaction matching will be done based on sub-portfolio account number and not sub-portfolio name).
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). See the ACCT key comments for more information.
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.
INOTE	investment note	Use the INOTE key to import an investment note when using NOTE as the TRAN value. See the "NOTE" example below.
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
DIST_X	value	(not used)*	(not used)*	distributed foreign tax type of distribution
DIST_1	value	(not used)*	(not used)*	distributed user-defined 1 type of distribution
DIST_2	value	(not used)*	(not used)*	distributed user-defined 2 type of distribution
DIST_A	value	(not used)*	(not used)*	distributed accretion type of distribution
DIST_T	value	(not used)*	(not used)*	distributed amortization 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
REINV_X	price	shares	commission	reinvested foreign tax type of distribution
REINV_1	price	shares	commission	reinvested user-defined 1 type of distribution
REINV_2	price	shares	commission	reinvested user-defined 2 type of distribution
REINV_A	price	shares	commission	reinvested accretion type of distribution
REINV_T	price	shares	commission	reinvested amortization type of distribution
SPLIT	ratio	(denominator)	(not used)	split. BNUM (denominator) is optional, and specifies the denominator of the split transaction. If BNUM is omitted (zero or any negative number) the denominator defaults to 1. Example, a 3 for 2 split has a ratio of 1.5 and a denominator of 2. If the denominator is omitted in this example, the split is recorded as a 1.5 for 1 split.
TXOUT	price	shares	(not used)	transfer out, price is 'market price'
TXIN	price	shares	cost basis (see notes)	transfer in. price is 'market price'. cost basis in the CNUM field can be specified in either of two formats: Format 1: cost basis per share The cost basis price specified will be used for both the tax cost basis and OOP cost basis. The date acquired will be the same as the transfer in date. Format 2: MM/DD/YY:tax basis per share:oop basis per share The date acquired, tax basis price, and oop basis price are all specified in the CNUM field. The date acquired must be specified in the MM/DD/YY format, followed by a colon (:), followed by the tax basis price, followed by another colon, and finally the oop basis price. See the examples below for an example of each format.
NOTE	(not used)	(not used)	(not used)	investment note. Not a transaction memo, but a note on the investment itself. See the "Notes" data type in the Data Register. (see example)

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.

* = For all of the DIST_* TRAN values, BNUM and CNUM are not required, but can be used to specify distributions per share when no shares will be owned. Normally, you record distributions in terms of values. When there are no shares owned though, you may wish to record a 0 value distribution, and enter a distribution per share. To supply a distribution per share figure, enter 1.0 for BNUM and the distribution per share amount in CNUM. This can be useful when importing data for investments you do not own, but wish to include the effects of distributions in performance metrics. If you own shares on the distribution date, any supplied CNUM distribution per share amount is ignored, and the distribution per share is always auto-calculated from the distribution value and shares owned. For related background on this issue, see the Distribution Per Share Prompt Dialog.

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:

Examples:
FORMAT STRING	IMPORTED LINE	EXPLANATION
UD,SYMB,TRAN,ANUM,BNUM,CNUM	971015,INTC,BUY,25 3/8,500,33	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.
UD,SYMB,TRAN,ANUM,BNUM,CNUM	971015,INTC,BUY,25.375,500,33	Same as the above example, but shown with a decimal share price. A fractional share price can be imported when the delimiter between fields is not a space.
CNUM;BNUM;XX;ANUM;TRAN;MM/DD/YY,SYMB	0;0;junk;75.50;DIST_D;10/15/97,INTC	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.
UD ANUM BNUM CNUM !REM my spreadsheet format	971015 27.25 200 33	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.
UD,SYMB,TRAN,ANUM,BNUM,CNUM,ASSET,ITYPE	971015,INTC,BUY,25 3/8,500,33,Large Cap Stock,Stock	Demonstrates the use of the ASSET and ITYPE keys.
UD,ACCT,SYMB,TRAN,ANUM,BNUM,CNUM,GOAL,CURRENCY	971015,12345,INTC,BUY,25 3/8,500,33,High Risk,$	Demonstrates the use of the ACCT, GOAL, and CURRENCY keys.
Transfer In/Out Examples:
FORMAT STRING	IMPORTED LINE	EXPLANATION
SYMB,TRAN,MM/DD/YY,ANUM,BNUM,CNUM	GOOG,TXIN,02/03/09,100,10,01/02/08:95:83	A transfer in of 10 shares at a market price of 100 occurred on February 3, 2009. This example is using format 2 for the CNUM field, where the date acquired is specified as January 2, 2008, the tax basis price is 95, and the oop basis price is 83.
SYMB,TRAN,MM/DD/YY,ANUM,BNUM,CNUM	GOOG,TXIN,02/03/09,100,10,95	A transfer in of 10 shares at a market price of 100 occurred on February 3, 2009. This example is using format 1 for the CNUM field, where only a cost basis price of 95 is specified. The date acquired will be recorded as February 3, 2009. Both the tax and oop cost basis prices will be 95
SYMB,TRAN,MM/DD/YY,ANUM,BNUM,CNUM	GOOG,TXOUT,04/06/09,110,5,0	A transfer out of 5 shares occurred on April 6, 2009 at a market price of 110.
Investment Note Example:
FORMAT STRING	IMPORTED LINE	EXPLANATION
SYMB,TRAN,MM/DD/YY,ANUM,BNUM,CNUM,INOTE	BTC-USD,NOTE,12/6/21,0,0,0,My financial planner recommended having 5% of my holdings in this	An investment note dated December 6, 2021, showing an example of the NOTE transaction type. Even though the ANUM, BNUM, and CNUM values aren't used, they must be present in the format string and the importing data. The note itself is in the INOTE field.

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.

Any line in the importing file beginning with the # character is ignored, as these can be used to store comments in your import file.

By default, importing transactions are matched up with investments based off the SYMB key only. You can optionally also qualify this matching with the NAME field be setting a registry option at:

HKEY_CURRENT_USER\Software\Beiley Software\Fund Manager\CurrentVersion\miscprefs

Use "regedit" to create a string value named "namequalifiesinv" and set this value to 1 to require both the SYMB and NAME fields match when updating investments. If both do not match, a new investment will be created.

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 ACCT or PORT keys 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.
Import Options	Specify whether to skip transactions that are already recorded. Also specify if new investments should be created when a matching investment could not be found. Fund Manager matches investments based on the symbol investment property, and the symbol in the importing file.

See Also