Importing and Reconciling Open Tax Lots Video - This video contains information on multiple aspects of importing and reconciling tax lots. Note: To access the video, right-click on the hyperlink and select Open in new window.
Morningstar has developed a cost basis reconciliation tool that allows users to reconcile the cost basis and lot information in Morningstar Office compared to that of certain custodians. This topic covers how to import and reconcile these lots in Morningstar Office. This can be done as part of the regular import process.
The following topics are covered:
What is a tax lot
What the advantages are of importing and reconciling tax lot data, and
How to import and reconcile tax lot data.
What is a tax lot?
In Morningstar Office, a portfolio’s realized and unrealized gains and losses are calculated based on the per-share cost basis for a security. The per-share cost basis is the price at which the share, or a “lot” of shares, was purchased.
The original purchase date of shares (or the “lot” of shares) can also be used to differentiate between long and short-term gains and losses in reports.
Note: Performance calculations (both IRR and TWR) are not dependent on per-share cost basis.
The per-share cost basis is usually populated in advisors’ accounting systems based on their data. This data is usually imported from the custodians’ or data aggregators’ transaction files and/or can be manually entered by editing the transaction.
When cost basis and lot information is reconciled, the tool is basically comparing everything in Morningstar Office’s accounting system to the cost basis file from the advisors’ custodian(s).
Note: Be sure to download the cost basis file from your custodian.
The cost basis file from a custodian on a particular day contains the cost basis and lot information of all the positions that are held across portfolios as of that particular day.
For which custodians can cost basis lots be reconciled?
The following table details which custodians offer importing and reconciling cost basis lots, and which file(s) you need to be sure to download from each custodian:
|
For this custodian... |
Be sure to download this file... |
Additional Information |
|
Fidelity Wealth Central |
fimmddyy.UGL |
This file must be requested. A complete Friday file is available to download on Monday. A file is available other days of the week but it will only contain new open positions not in the Friday file. |
|
Pershing InfoDirect |
mmddyy.POTL |
This file must be requested. A complete file is available daily. |
|
Schwab |
CRSyyyymmdd.ULT - Unrealized Gain/Loss Open Lot Taxable file CRSyyyymmdd.ULN - Unrealized Gain/Loss Open Lot Non-taxable file |
Complete files are available daily. However, if there is no transaction file, then they aren't sent. |
|
TD Ameritrade |
TDyymmdd.CBL - Cost Basis Open Lot file |
A complete file is available daily. |
How does cost basis reconciliation fit within the usual import process?
Before conducting cost basis reconciliation, users need to both initialize client positions (or have their previous database converted to Morningstar Office), and import any transactions on and prior to the date of your cost basis files. After initializing and reconciling the transaction data, you can bring in the cost basis data.
Note the following important points:
The easiest time to perform the cost basis reconciliation is immediately after the initialization process, as this allows you to use the Split Initialized Lots function, based on the existing deliver-in transactions prior to any sell transactions posting.
Once a sell transaction posts for a holding, you can no longer use the Split Initialized Lots function, which is the optimal method, since this provides cost basis for the entire history of an account in Morningstar Office.
Only users doing an initial import for a Custodian that offers cost basis reconciliation within Morningstar Office will be able to take advantage of this optimal timing.
What do I need to do before starting cost basis reconciliation?
Before starting the cost basis reconciliation process, you need to verify the tax lot method setting in Morningstar Office matches what you are using at your custodian. If you are not sure what method you are using at your custodian, please contact the custodian for verification. Click here 
for instructions on establishing your tax lot method settings in Morningstar Office.
What information is reported in the Lots blotters when I import and reconcile cost basis lots?
|
Blotter |
Description |
|
Imported Open Lots |
This information is pulled from the cost basis file sent by the custodian. This represents the lot data your custodian is reporting. |
|
Matched Lots |
These accounts have successfully reconciled cost basis and lot information, and your unrealized gain/loss calculations in Morningstar Office will be accurate when compared to the custodian. They are simply listed here for informational purposes and no further action is needed. |
|
If a security has mismatched lot information, this means the lots of a particular security in Morningstar Office are not matching the lots held in the custodian's cost basis file. The cost basis reconciliation tool can help fix this discrepancy by splitting lots or re-initializing lots in Morningstar Office to match those from the custodian. Click here |
|
|
This means the cost basis of a particular lot in Morningstar Office is not matching up with the cost basis of the lot in the custodian's cost basis file. The cost basis reconciliation tool can help fix this discrepancy by allowing you to create adjust cost transactions that will allow you to adjust the cost of lots that may not have the same cost basis as is reported in the cost basis file. Click here |
|
|
Excluded Holdings |
If you exclude a holding from the normal share balance reconciliation or from cost basis reconciliation, it will show in this blotter. These holdings would typically be ones not held at the custodian, but are in the client’s account in Morningstar Office. |
What methods of reconciliation are available?
After you have imported and posted your client data, you are taken to the Lots blotter. You can use any of the methods of lot reconciliation which are built into the Lots blotter. The following table explains the options available when reconciling lot information under the Mismatched Lots and Mismatched Cost Basis blotters.
|
Method |
Description |
|
Split Initialized Lots |
This function breaks up one, complete lot (represented by a deliver-in of shares) in Morningstar Office to multiple deliver-ins. Each deliver-in represents one lot with a unique cost basis and original purchase date. Note: The original purchase date is not the trade date of the transaction being split. The trade date remains the date of the deliver-in, but the original transaction date field is populated with the custodian’s original purchase dates. This tool works only for open positions that have not been partially closed since they came into the account (on the deliver-in date). To address lots for partially closed shares, you might have to manually edit the deliver-in record in Morningstar Office, unless you have cost information for the closed position(s). Click here Reasons Why This Tool May Not Work
|
|
Re-initialize Lots |
This tool should only be considered if Split Initialized Lots does not work. This tool will debit out your lots with missing information and credit back in each lot with the correct cost basis as of the day you are reconciling. If you adjust your lot information this way, your gain/loss reports will only be accurate or complete from this day going forward, which would be the trade date of your new Credit of Security transactions. Historical gain/loss reports may not be accurate. Click here Reasons Why This Tool May Not Work
|
|
Manually Edit a Lot |
If the number of lots from the custodian matches the number of lots in Morningstar Office, you may need to simply adjust data such as the Acquisition Date, or Number of Shares, or Price. Click here |
If you are concerned about reporting only on unrealized gains and losses, then, when the Split Initialized Lots tool can’t be used, the Re-Initialize Lots option would be sufficient, since the tool simply re-establishes your open lots to match your custodian as of the day you are reconciling. Please keep in mind that your gain/loss reports will only be correct from the date of the credit/debit of security transactions that are generated forward. These transactions will be dated as of the date of your current import.
If, however, you are concerned about reporting realized gains and losses, then a combination of splitting lots and manually editing deliver-in transactions will be required to ensure accurate reporting at the earliest start date. Using this option will give you the most accurate historical data.
Listed below are reasons reconciliation discrepancies will display in the Mismatched Lots blotter.
When you first started importing into Morningstar Office, you most likely went through the initialization process, which creates a single Deliver-in transaction for each holding in your accounts using the position file provided by your custodian. This initial lot will most likely not reconcile with your custodian lot file because your holding is made up of multiple lots. This requires the Split Initialized Lots option.
Many custodians do not provide the lot matching method on their closing transactions (FIFO, LIFO, Specified, etc.). For this case, Morningstar Office relies on the default matching method setting for the holding. If the closing transactions’ matching method does not match the Morningstar method, the Morningstar open lots may not match the custodian open lots. This requires the View/Edit Transaction update to edit the closing transaction.
Many custodian trading platforms have sophisticated tax lot optimizer algorithms that specify which lots to close. This requires the Specify Lot option to be used on the Sell transactions. Click here for information on how to do this. If you have the Specified accounting method set for this security type or holding within your account, you can select the lot to deplete during the import process by using our Specify Lot feature within the Transactions blotter.
Cost Basis step up due to death. If this is the first time the security is in the account and the transaction is a deliver-in, the Split Initialized Lots could be used. However, if the holding existed previously in the account, then you would need to Re-initialize Lots or Manually edit the lot.
How do I reconcile using the Split Initialized Lots method?
Splitting Initialized Lots is a process by which users can simply override open tax lot data (that is either per share cost basis, number of shares, or acquisition date) in Morningstar Office with the data the custodian provides. This detailed and precise process results in Office data most accurately mirroring the custodian data.
Users most likely to use this method want to account for historical transactions that affect tax lots or who want to produce accurate realized gain/loss reports.
Partially closed lots cannot be split, however you can manually update the transactions related to those lots.
Here’s a simple and straightforward example of why you might consider splitting open lots. After you initialize or import your client and account data, your client owns 100 share of ABC fund which, in Office, is accounted for with one Deliver-in transaction of 100 shares. But, after importing your tax lots, you find out that the 100 shares was really the result of two buys, we’ll say 75 shares on a certain day and 25 shares on a different day. With the Split Initialized Lots method, Office provides the functionality to break that single Deliver-in of 100 shares into 2 Deliver-ins with the correct acquisition date, per share cost basis, and number of shares.
To split initialized lots, do the following:
On the Mismatched Lots blotter, select one security with mismatched lots.
Click on Split Initialized Lots. The Unmatched Deliver-ins dialog box opens. The Unmatched Deliver-ins dialog box shows the lot data currently in Morningstar Office, as well as the data provided by the custodian.
Click Split. The Deliver-in Date and Open Shares columns in the Office Lots area of the grid view will populate with the custodian-provided data. Click Next to close the Unmatched Deliver-ins dialog box.
The next screen displays the updated cost basis on the Lot Acquisition Date and Cost Per Share fields; this allows you to review the new data for the security or securities. If you are satisfied, click the Post button. This will post the transactions to the account in place of the original Deliver-in transactions.
A confirmation appears; click OK. These lots will now appear in the Matched Lots blotter.
Repeat this process for the remaining accounts.
How do I reconcile using the Re-Initialize Lots method?
The Re-Initialized Lots option allows you to create a new baseline using the open lot and cost data from your custodian. However, you should carefully consider whether or not to use this option.
The Re-Initialized Lots option will generate a single Debit transaction for the entire open holding(s) you have selected in Morningstar's system. Additionally, the option will generate multiple Credit transactions corresponding to each open lot record from your custodian. Each Credit transaction will be created with the original cost that your custodian is providing. The end result of the holding amount(s) will be the same, but now your open lot and cost data will be in sync with your custodian.
Users most likely to use this method might want to create a new beginning point with custodian provided tax data; produce unrealized gain/loss reports; have a position with months or years of complicated transactions which can’t be easily tracked, audited, or deciphered; or the user doesn’t want to manually edit transactions for positions containing partially closed lots.
There are some items you should consider before using this Re-Initialize Lots option.
Creating Debit and Credit transactions as of your import lot file will only ensure that your lot and cost data is correct moving forward. This option will not correct any historical transactions that have occurred prior to this new baseline you are establishing.
Therefore, if you run a Gain/Loss report for a date range that spans across this new baseline, the result could be inaccurate. Only a date range after this new baseline should be considered.
If you want to correct your entire transaction history for the un-reconciled lots, you can start with the Split Initialized Lots option to split your initial Deliver-in transaction into multiple lots with correct cost. If you are unable to Split Initialized Lots, you can manually edit the lots, in addition to verifying your existing, closing transactions to ensure the tax lot accounting method is correct and Morningstar has closed the correct lot(s). This is a more time-consuming approach.
Both of the two reconciliation methods are precise in that the custodian’s data is brought into Office. However, the more intricate process of splitting initialized lots and manually adjusting exceptions results in accurate gain/loss reporting that starts at an earlier date.
To re-initialize lots, do the following:
From the Mismatched Lots blotter, select one security with mismatched lots.
From the toolbar above the grid view, click the Re-initialize Lots icon. A dialog box opens, describing the process and consequences of re-initializing.
After reading the message, click OK to continue. The Re-initialize Lots dialog box opens.
The Re-initialize Lots dialog box displays the updated cost basis on the Lot Acquisition Date and Cost Per Share fields. Review the debits and credits provided by the custodian, then click Post. A confirmation message opens.
To clear the message, click OK. These lots will now appear in the Matched Lots blotter.
Repeat this process for the remaining accounts.
Manually editing lots is an appropriate option to reconcile lots as long as the user can provide the data and enter it in Office. In this method, a user can view an un-reconciled position and edit the transactions manually with the correct number of shares, share price, or acquisition date.
Manual editing is generally used in conjunction with the Splitting Initialized Lots reconciliation method because it is not uncommon to have partially closed lots as part of the import file. And, as stated previously, the primary reason for using the splitting lots reconciliation method is to generate accurate realized gain loss reports.
To manually edit a lot, do the following:
Complete the standard import process and post the data to Morningstar Office; you are automatically taken to the Imported Open Lots blotter.
From the toolbar above the grid view, click the Reconcile icon. The Choose Accounts to Reconcile dialog box opens.
In the Choose Accounts to Reconcile dialog box, select one or more accounts to reconcile.
Click the Reconcile button. A warning message opens, informing you some lots did not reconcile.
To clear the warning message, click OK. The Mismatched Lots blotter opens, listing the securities with mismatched lots.
Click in the View Transactions field for a security. The Transactions dialog box opens.
Using the data displayed in the Transactions dialog box, you need to determine on a case-by-case basis how to solve the mismatched lots. Solving the issue might involve right-clicking on a transaction and selecting Edit, or to create a New transaction.
|
Method |
Description |
|
Update Cost to Match Custodian |
This option is available in the Mismatched Cost Basis blotter only, and simply updates the Morningstar Cost Per Share with the custodian-provided Custodian Cost Per Share using an Adjust Cost transaction. Click here Reasons Why This Tool May Not Work
All lots that were purchased prior to their “covered” date are bucketed under one cost basis, and those lots purchased after the dates above are bucketed under a different cost basis. Morningstar Office does not support Covered/Uncovered Average Cost tracking. To identify if this is a covered/uncovered issue, verify if you are using Average Cost at your custodian for this holding. If so, check the Lot Acquisition Date and Custodian Cost Per Share columns in the Mismatched Cost Basis blotter. If the lots with a date prior to the implementation dates above are showing one average cost basis and the lots on or after the implementation dates are showing another, this is Covered/Uncovered Average Cost. These lots will either need to be ignored or excluded from Cost Basis Reconciliation. To exclude a holding from cost basis reconciliation, you can put a check next to the line item, click on Action, and choose Exclude Holding.
|
|
Manually Edit a Lot |
If the number of lots from the custodian matches the number of lots in Morningstar Office, you may need to simply adjust data such as the Acquisition Date, or Number of Shares, or Price. Click here |
Listed below are reasons reconciliation discrepancies will display in the Mismatched Cost Basis blotter.
The tax lot method setting in Morningstar does not match the custodian setting. This is one of the most common reasons why cost basis is mismatched. Please make sure your Tax Lot Method setting in Morningstar Office matches what you are using at your custodian.
The security may have incurred a corporate action and the cost calculation methodology may be slightly different. For example, there may be a difference in the fair market value calculation. This requires the Update Cost to Match Custodian option or you can manually edit the transactions with the correct cost basis.
There may be a wash sale cost adjustment that Morningstar Office has not considered. This requires manual adjustments or can be ignored if you are not accounting for wash sales in your data.
There may be a Return of Principal cost adjustment that Morningstar Office has not considered. This requires the Update Cost to Match Custodian option.
Missing original cost basis, but the acquisition date is entered. Use the Update Cost to Match Custodian method or Manually edit the lot.
Amortization/Accretion difference. Click here for more information on how to use our Amortization/Accretion Wizard to resolve this discrepancy.
How do I reconcile using the Update Cost to Match Custodian method?
To use the Update Cost to Match Custodian method to reconcile your lot data, do the following:
Complete the standard import process and post the data to Morningstar Office; you are automatically taken to the Imported Open Lots blotter.
From the toolbar above the grid view, click the Reconcile icon. The Choose Accounts to Reconcile dialog box opens.
In the Choose Accounts to Reconcile dialog box, select one or more accounts to reconcile.
Click the Reconcile button. A warning message opens, informing you some lots did not reconcile.
To clear the warning message, click OK.
In the Mismatched Cost Basis blotter, select one or more of the securities.
From the toolbar above the grid view, click the Update Cost to Match Custodian icon. The Update Cost to Match Custodian dialog box opens. Note: Selecting multiple or all securities makes this process more efficient, as you will not have to return to this step.
In the Update Cost to Match Custodian dialog box, click Post. A confirmation message opens.
To clear the message, click OK.
See Also
Importing and Reconciling Tax Lots and Cost Basis FAQs