Excel export / import guidelines for shareholder information

Alerts and notices

Consider the following guidelines when modifying the Microsoft Excel workbook data you want to import into UltraTax CS.

  • Always start by exporting shareholder information spreadsheet data into Excel. UltraTax CS assumes that the initial Excel workbook template was generated via the process of exporting the shareholder information spreadsheet from UltraTax CS. Though we highly recommend it, you do not need to enter all of the data into the Shareholder Information spreadsheet (in the UltraTax/1120 Shareholder Information window) before you export the spreadsheet into Excel.
  • Always save the Excel workbook before importing the data into UltraTax CS. This ensures that all recent changes will be imported into UltraTax CS.
  • Subsequently created export / import spreadsheets will always overwrite any previously created spreadsheets by the same name.
  • You can export shareholder client data, stock basis, loan basis, suspended losses, and change of ownership amounts from one UltraTax/1120 client to an Excel workbook, and then subsequently import that data from Excel into a different UltraTax/1120 client.
  • For the purpose of importing data into UltraTax CS from Excel, UltraTax CS assumes you have not modified the order of the Excel worksheets in the workbook. The worksheets are imported into UltraTax/1120 in the order in which they were initially exported to Excel, which is as follows:
    1. 1120 Shareholder Information
    2. Shareholder Basis
    3. Weighted Average
  • You can insert or delete rows in the workbook. You must, however, maintain consistency within the workbook. Therefore, you must insert or delete rows in all three worksheets in the identical location.
  • You cannot change row height and column width.
  • You cannot insert or delete columns in the 1120 spreadsheet worksheet.
  • You can enter a maximum of 24 changes of ownership in the Weighted Average worksheet. If more than 24 changes of ownership are entered, only the first 24 will be imported into UltraTax CS.
  • You can enter a maximum of 400 shareholders in the 1120 Shareholder Information worksheet. If more than 400 shareholders are entered, only the first 400 shareholders will be imported into UltraTax CS.
  • The change of ownership dates must be within the UltraTax CS client's tax year.
  • You should enter override percentage ownership in the following format: xx.xxxxxx. For example, if a shareholder was allocated 28.5685 percent, you would enter the percentage as 28.568500.
  • You should enter units of ownership in the following format: xxxxxxx.xxx.
  • UltraTax CS accounts for change of ownership dates and shares in the Weighted Average worksheet. You should enter change of ownership dates sequentially.
  • The Supplemental Information statement dialog detail is not exported or imported. When importing from Excel, total amounts are imported into the Shareholder spreadsheet. You can then open the Supplemental Information statement dialog and update the statement detail.

    Note: If the color of the Statement dialog Statement dialog button button is purple, then the amount imported from Excel does not equal the amount within the statement dialog. Open the statement dialog and update the detail information.

  • The following table summarizes valid entries for data in the 1120 Shareholder Information worksheet.
Column Entry
SSN/EIN xxx-xx-xxxx = Social Security Number
xx-xxxxxxx = Employer Identification Number
A = Applied For
D = Died
M = Amish

Note: If a nine-digit number is entered without dashes, the number will be formatted as a Social Security number when imported into UltraTax CS.

Email address Enter full email address
Phone number xxx-xxx-xxxx

Note: If a ten-digit number is entered without dashes, the number will be formatted as a telephone number when imported into UltraTax CS.

Tax matters person X

Note: Select only one shareholder as the tax matters person.

Final K1 X
Rounding shr X

Note: Select only one shareholder as the rounding shareholder.

1040 Client X
Officer X
Print shr letter in Spanish X
Exclude shr from 1120S p1 count X

Note: If you are not going to import the Excel file back into UltraTax CS, you can make modifications to the file in accordance with Microsoft's Excel guidelines.

Related topic: Excel Export / Import utility for shareholder information overview