Import Fund/Pool Values
The Import Fund/Pool Values function updates values that can also be maintained using Maintain Fund/Pool Values.
This information can be used by CashTrac CGA Market Values when updating gift annuity market values based on a total value for the annuity pool.
For licensees of GiftWrap's Pooled Fund Administration module, this information also can be used to determine distributions and unitize new gifts, to display market value, expense, and income information on the Annual Report and in the computation of a pooled income fund's yearly rate of return. The deduction for a gift to a pooled income fund is computed using the fund's highest yearly rate of return for the three years prior to the year of gift.
If a Valuation Date in the file is already stored for the fund/pool identified in the file record, the values for that date stored in GiftWrap are overwritten by the corresponding values found in the file. If a Valuation Date in the file is not already stored for the fund/pool, the Valuation Date and associated values in the file are added as a new record in GiftWrap.
The import file must meet the format specifications described in the Import file requirements section below.
1. Choose Utilities > Import Fund/Pool Values in the Menu bar.
2. Mark the First line is column header check box if your file includes column headers (strongly recommended).
3. Click 
.
4. Browse to the file whose contents you wish to import. Click Open.
5. Click Import. If you see an error log, see Notes below.
6. Once the import completes, a message will report the number of records in the file that were imported successfully and the number that were not imported.
Import file requirements
Import Fund Values requires the import file to be a comma-delimited text file with a .csv extension. Numbers should not include commas and any text value, such as an account number, must be contained in quotes if it includes a comma. The easiest way to save a comma-delimited file is to work in Excel and, when done, save the file as type .csv. When you save an Excel file in .csv format, Excel will automatically place quotes around any text value that contains a comma.
Each line in the import file should correspond to a separate gift record in GiftWrap. Each line must contain a valid gift type ID, value type, aggregate value, and value date, and value date. In addition, if the gift type is PIF, the line must contain a value for at least one of the following that matches a value in tblPif: Tax ID #, PifAccount1, PifAccount2, PifAccount3 or SubAccountID (which is the PifID). If the gift type is CGA, a value is required for SubAccountID (which is the AnnuityPoolID).
In order for a market value to be imported, all gift identifiers supplied on the line must match the corresponding values in a pooled income fund or gift annuity pool record in GiftWrap. Import Fund/Pool Values will import values only from those lines in the import file that meet these criteria.
Optional column headers
While column headers are not required by GiftWrap, we strongly recommend that you include them in the file. Column headers can simplify proper formatting of the import file. They also make the file's contents easier to understand.
The column headers in the Import Fund/Pool Values file can use any names you want, but should relate well to the field they label. For example, use a header such as GiftTypeID to label the Gift Type ID field. You can use uppercase or lowercase letters in the column headers. As with all records in the file, each header must be separated by a comma.
File format
Each line of the import file must be laid out as follows:
Gift Type ID, PIF Tax ID #, PIF Account 1, PIF Account 2, PIF Account 3, SubAccount ID, SubAccount Value Type ID, Aggregate Value, Per Unit Value, Valuation Date
For illustration purposes, the example below contains more values than necessary to uniquely identify the pooled income fund for which values are being imported.
For example, a line containing a Gift Type ID of "9" (9 indicates a PIF), a PIF Tax ID # of "123456789", an Account 1 of "100", a PIF Key of "2", a Value Type ID of 1 (1 indicates a market value), a Fund Value of $100,000, and a Fund Valuation Date of 12/31/2007 would look like this:
9,123456789,100,,,2,1,100000,,12/31/2007
The same line, but without Account # and PIF Key values would look like this:
9,123456789,,,,,1,100000,,12/31/2007
A five line file might look like this, for example:
9,123456789,,,,,1,100000,,12/31/2003
9,123456789,,,,,1,110000,,12/31/2004
9,123456789,,,,,1,990000,,12/31/2005
9,123456789,,,,,1,100100,,12/31/2006
9,123456789,,,,,1,102000,,12/31/2007
Each line of the file is evaluated independently. Although it is typical for all lines of an import fund market values file to use the same value(s) to identify each gift record, they don't have to. One line could use the Tax ID #, while another line uses PIF Key.
Gift Type ID format requirements: There are two valid Gift Type IDs: 2 and 9. Use 2 to indicate a CGA (gift annuity pool) and a 9 to indicate a PIF (pooled income fund).
Tax ID # format requirements: Tax ID #s may contain hyphens. You may express "12-3456789" as "123456789" or "12-3456789" in the file.
Aggregate Value and Per Unit Value format requirements: Dollar amounts must not contain commas or dollar signs. They may contain a decimal point. Express "$10,000.00" as "10000" in the file. Express "$12,345.67" as "12345.67" in the file. A negative value is allowed for Aggregate Value only if the Gift Type ID is 2 (a CGA pool) and the Value Type is 5 (Gain/Loss).
Valuation Date format requirements: The valuation date must be expressed in mm/dd/yyyy or m/d/yyyy format. You may express July 1, 2009 as "7/1/2009" or "07/01/2009". The date must be valid: the month cannot exceed 12, the day cannot exceed the maximum days in the indicated month, and the year must be within 1800-9999.
SubAccount Value Type ID format requirements: SubAccount Value Type ID has five valid values: 1, 2, 3, 4, or 5. These values correspond to types of values as follows:
1 = Market value
2 = Income
3 = Fees/Expenses
4 = Interest/Dividends
5 = Gain/Loss
If the Gift Type is CGA, Value Types 1-5 are valid. If the Gift Type is PIF, only Value Types 1-3 are valid.
Notes:
· If you see an error log, we recommend that you fix the errors in the file that it identifies, then run Import Fund/Pool Values again.
If you click Continue, Import Fund/Pool Values imports all valid records in the file and ignores all invalid records in the file.
You can print the error log or save the log in a file that you can print or review later.