Captools/net Documentation

Data Tools

Data Tools

Previous topic Next topic  

Data Tools

Previous topic Next topic  

Data Tools are commands which trigger data operations which are performed on the server.  Unlike the operations described in the prior section under Batch Ops, you are released to do other activities on the CT/net desktop while the account processes proceed.  Data Tools are accessed by the Tools command on the CT/net menu:

 

DataToolsCommand    

 

 

 

Once you are on the accounts view, if you select more than one account, prior to executing the "Tools" command as in the following example, these will be the accounts processed in the subsequent operation, unless you specify filters in setting up the particular operation.  If only a single account is selected, all accounts will be processed unless filters are subsequently specified.

 

While the processing is ongoing, the progress is viewable via a series of log messages appearing in the browser window once you have commenced the process.  The log window may be minimized to permit you to perform other operations while you let this operation complete. When the processing is finished you will see the results of the data operations in your CT/net tables upon doing a "File/Save & Refresh" operation.

 

Transfer From Blotter

 

This data function moves data held in the transaction blotter into the applicable account portfolio transactions.  This can be executed after performing an import into the transaction blotter.  An account selection dialog will be presented in the browser when you start this operation.  The accounts represented in the transaction blotter will be pre-checked, so there is no need to select accounts.  A transfer of transactions from the blotter can also be done concurrent with performing a batch valuation simply by checking this option when

 

ToolsTransferBlotter

 

 

Rollback to Blotter

 

This data function moves account transactions out of the account portfolio into the account transaction blotter.  This gives you the opportunity to "un-do" groups of transactions which may contain estrrors, without needing to enter each account portfolio separately to perform the correction.   To execute this function check off the desired accounts, specify a date range, and if necessary, specify the ticker symbols and TAC codes you want to have included or excluded from the operation:

 

DataToolsRollback

 

If the option to Move Transactions to the Transaction Blotter is unchecked, then the transactions will be effectively deleted from the data base entirely.

 

 

Get Curr Quotes, Get All Histories, etc.

 

These function download quotes, etc. or open the browser to a web page for the currently selected security record from your default download service.  If no service has been selected no action occurs.

 

 

Freeze/Unfreeze Portfolio by Date

 

Portfolio records are generally automatically "frozen" during the valuation process once they become older than the number of months specified in the program preferences, "other settings".   Freezing prevents you from inadvertently changing the records through a manual edit, although certain computations such as tax lot assignments and estimated tax computations are allowed to change frozen records.  If you need to perform edits on frozen records in a single portfolio or a few portfolios, it is best to use the "View/Freeze" and "Unfreeze" functions to changed the frozen property of records in those portfolios.  However, if you want to change the frozen properties in many portfolios, you should used the "Processes/Freeze/Unfreeze" function.

 

The Freeze/Unfreeze function, like most of other batch functions except the batch valuations and batch trade generation functions, runs though a web-based interface appearing as follows:

 

DataToolsFreezeUnfreeze

 

To Freeze or Unfreeze, simply specify the Freeze Date, and then select the accounts against which you want to perform the operation.  You may use a filter to specify accounts by one or more of the fields appearing in the table above, such as account type or group.  The Overwrite action should generally be "Overwrite", as long as you know that you are the only user processing records in these accounts, otherwise, "Skip", "Ignore" or "Prompt" would be safer choices to avoid overwriting records in the process of being modified by other users.

 

Upon clicking on the "Start Batch Operation", the CTServer will commence freezing all records prior to and including the specified date and unfreezing all records dated thereafter, for the specified accounts.

 

 

Security Spin-Off

 

The Security Spin off function permits you to generate the transactions which are required when holders of a security, typically a common stock, receive shares in another security due to part of the original security's business being spun-off.  The spin-off function also uses a web-based user interface:

 

DataToolsSecSpinOff

 

In this example, the parent company, with symbol "XYZ" spins off 20% of its business to shareholders in the form of 0.25 shares of stock in the new company, "ABC" for each original share of "XYZ".  The market price at the time of the spin off was $85.27, and a taxable one time dividend of $1.25 per share of XYZ is also paid to shareholders.

 

Upon executing "Start Batch Operation" the CTServer will commence processing all of the selected accounts  (standard Captools/net web account selection, not shown in this example).  Any accounts holding XZY on the spin-off "Ex-Date" will have RCV (receive) transactions generated for the "ABC" shares and for the cash dividend.  Any fractional shares of "ABC" will be converted to Cash In Lieu (CIL) transactions.  Additionally, the cost basis of the original XYZ shares will be reduced by 20%, with that amount now being attributed to the "ABC" shares.

 

Resulting transactions are sent to the Transaction Blotter  in Pro and Enterprise versions if the Send to Trans Blotter is checked, otherwise it is sent to the relevant portfolios.

Cash in Lieu (CIL) transactions are generated for partial shares received.  CIL transaction quantity and amounts may differ slightly from the actual amounts received due to differences in rounding assumptions.  It is therefore important that you verify that resulting transactions match the actual quantities and amounts received into your accounts by the custodian.

 

 

Security Merger Generation

 

The Security Merger function permits you to generate the transactions which are required when holders of a security, typically a common stock, are forced to exchange shares for those in another when the security is bought by another company, with payment, at least in part, in the form of shares.

 

DataToolsMergerGen

 

The merger date is the date that will be used for all transactions generated.  The Tendered Price is the price at which the transaction takes place, and the Quantity/Share is the number of shares in the buying firm that will be received for each share tendered in the firm being bought.  Cash/Shr is any cash payment that may be received in connection with merger, with share basis being that of the shares being received.  This will be coded as a dividend and will be assumed taxable if Cash is Taxable is checked.  Generally, all of this information will be published in the tender offering document, or after the boards of directors have approved the merger in a document sent to the shareholders of the firm being bought.

 

Resulting transactions are sent to the Transaction Blotter  in Pro and Enterprise versions if the Send to Trans Blotter is checked, otherwise it is sent to the relevant portfolios.

Cash in Lieu (CIL) transactions are generated for partial shares received.  CIL transaction quantity and amounts may differ slightly from the actual amounts received due to differences in rounding assumptions.  It is therefore important that you verify that resulting transactions match the actual quantities and amounts received into your accounts by the custodian.

 

 

Distribution Generation

 

The Distribution Generation function adds cash and share dividends to portfolio transactions for the specified accounts based upon distribution data contained in the Security History/Distribution records.  Most Pro and higher level users will not need to use this utility because their distribution data will be imported through their Custodian/Clearing firm interface.  If you do need to use this utility, first make sure that the distribution records in your Security History/Distribution table are complete and correct.  Then select the accounts for which you want to generate distributions and specify a date range.

 

DataToolsDistributions

 

Resulting transactions are sent to the Transaction Blotter  in Pro and Enterprise versions if the Send to Trans Blotter is checked, otherwise it is sent to the relevant portfolios. Since rounding methodologies can differ between Captools/net and the payer of these distributions, you should take care to verify that results produced by Captools/net match those reported by the payer.

 

Money Market Funds and Foreign Cash - Securities designated as money market funds will have their distributions computed based upon the average daily balance for the relevant period with the distribution credited using the transaction code indicated in the distribution record.  Securities which are "foreign cash", i.e. whose symbol is identical to the identifier in the "Currency" field of the security in the Security Id record, will likewise have the distribution computed based upon the average daily balance and credited or debited using an IOB transaction code.  In either of these cases (Money Market Funds or Foreign Cash) the credit rate must be entered into the "Amount Per Share" field, and the debit rate in the "Quantity Per Share" field, and the "short rate" (if applicable) in the "Reinvest Price" field.   Foreign Cash credits and debits will only be computed for licenses operating at the Level 4 level and for accounts for which "foreign cash accounting" has been activated.

 

Share Distributions and Cash in Lieu - Distribution records with "SP+" in the Distribution records will generate "SP+" transactions when Distribution Generation is run.  If the computed number of shares include a fractional share quantity, this will be converted to a "CIL" (Cash in Lieu) transaction using the "Reinvest Price" specified in the Distribution records.  If the "Reinvest Price" is zero or blank, then no CIL transaction will be generated and the fractional share will be included in the quantity field of the "SP+" (Split) transaction.

 

Inclusions/Exclusions based on Security Type - You can include or exclude securities from the distributions based on security type instead of symbol, by using the notation "types=" in the "Symbols" field.  For example "types=cs,mf" would include/exclude securities of types "cs" and "mf" from the distributions.

 

 

 

Credit/Debit Interest Generation

 

This data function, available only to Pro/Enterprise licensees who opt for Level 4 features, computes interest on account credit or debit balances and credits or debits the interest to the account as appropriate.  The interest rates applied are those  specified in the interest rate records.

 

DataToolsCreditInterest

 

Resulting transactions are sent to the Transaction Blotter  in Pro and Enterprise versions if the Send to Trans Blotter is checked, otherwise it is sent to the relevant portfolios.

 

Money Market Funds "Interest" - Income payments on money market funds are usually technically considered dividends and not interest although they are computed like interest based upon daily balances.  Use the "Distribution Generation" function described in the topic above to generate these distributions.

 

Foreign Currency Credit/Debit Interest - Credit and debit to foreign currency holding in accounts using "Foreign Cash Accounting" can be implemented using the "Distribution Generation" function described above.

 

 

Currency Revaluation

 

This data function re-states all amounts and values in a portfolio to new amounts and values by applying the specified conversion factor.  All securities assigned the old currency symbol are also re-specified to be assigned the new currency symbol.

 

DataToolsCurrencyRevaluation

 

This function may never be needed by most users, however it may be essential for some due to national adoption of a new currency or re-valuation of the old currency.

 

 

Consolidate Split Transactions

 

This function is a "fix-up" function which will combine trade transactions which where imported from a custodian in "split" form.  E.g. a trade of 2000 shares for a single might be executed in 5, 10 or 20 smaller transactions on a given date.  Most custodians will provide this data as a single transaction at an average price.  However some will provide all the individual parts of the transactions, making this function of use to consolidate them into a single trade after the data has been transferred to the account portfolios.  Only same-date, same symbol transactions in the specified date range and within the specified price tolerance are consolidated.

 

ConsolidateSplitTrans

 

 

Account Data Transfer

 

This function facilitates moving data between accounts such as is required when splitting accounts into separate accounts, e.g. due to a divorce or estate inheritance, or merging previously separate accounts together, e.g. combining IRA accounts.

 

AcctDataTransfer

 

When splitting an account such as for parental account being divided between inheritors, the parental account would be the "Source Account".  The "Date" is the "as of date" for the transfer, and should be a date for which a valuation record has already been created in the source account. In the case where an entire account is being distributed, then the "Symbol" and "AcctId" fields would not be needed.

 

The Destination Accounts are those accounts that will be receiving shares from the source account in proportion to the percentages specified.

 

Clicking on "Start Data Operation" will cause "DLV" (deliver) transactions to be generated in the Source account on the "as-of" date and corresponding "RCV" transactions in the destination accounts.  Both the source and destination accounts will  be flagged (perturbed) to cause valuations downstream of the transactions to be recomputed.

 

If it is the intent to only partially distribute from the source account, the "Symbol" and/or "AcctId" fields can be filled to cause only transactions bearing the particular symbol or AcctId to be transferred to the destination accounts.  

 

If the "As-of" date is specified to be prior to the inception date of the source account, then no DLV or RCV transactions are generated.  Instead all transactions from the source account are moved to the destination account(s). This is particularly useful when merging accounts and you want all of the account history preserved for reporting purposes.

 

 

 

Bifurcate Lots

 

This function facilitates converting tax lots in one account or multiple accounts prior to or after a specified date.  This is primarily useful for the U.S. Tax law changes in 2010 which mandated custodian reporting of the cost component of sales transactions after 2012.  This law authorized separate treatment of transactions before and after a "cutoff date", which for Average lots can require separate averages for before and after the cutoff date.

 

BifurcateLots

 

 

Browser-Based Data Tools

 

Many of the aforementioned functions are also available through your browser by clicking on the Data Tools item on the Captools/net Home page to obtain the following menu:

 

BrowserDataTools