Form CMS-10320 CMS-10320.Issuer - FINAL Instructions and Instruments

Health Insurance Oversight System (HIOS) Excel
Data Submission Tool - Issuers
Technical Instructions

PRA Disclosure Issuerment

According to the Paperwork Reduction Act of 1995, no persons are required to respond to a collection of
information unless it displays a valid OMB control number. The valid OMB control number for this
information collection is 0938-1086. The time required to complete this information collection is
estimated to average ( 30.32 hours) or (1,819.2 minutes) per response, including the time to review
instructions, search existing data resources, gather the data needed, and complete and review the
information collection. If you have comments concerning the accuracy of the time estimate(s) or
suggestions for improving this form, please write to: CMS, 7500 Security Boulevard, Attn: PRA Reports
Clearance Officer, Mail Stop C4-26-05, Baltimore, Maryland 21244-1850.

Last updated May 5, 2010

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions

Table of Contents
1

Introduction ........................................................................................................................ 2

2

Issuer Data Entry Form Functions ...................................................................................... 3
2.1

Submission Requirements ............................................................................................ 3

2.2

How to Open the Issuer Form Workbook ...................................................................... 3

2.3

Circle Invalid ................................................................................................................. 6

2.4

Finalizing the Issuer Data Entry Form for Submission................................................... 8

2.5

Closing the file .............................................................................................................. 9

2.6

Password Protection ..................................................................................................... 9

3

Tips for Entering Data ...................................................................................................... 10
3.1

Issuer General Info Tab .............................................................................................. 10

3.2

Product Info Tab ......................................................................................................... 20

3.3

Geographic Coverage................................................................................................. 21

4

Troubleshooting and FAQ ................................................................................................ 23
4.1

FAQ’s ......................................................................................................................... 23

4.2

Submission Information .............................................................................................. 23

4.3

Support ....................................................................................................................... 23

5

Appendix A— Critical Errors ............................................................................................. 24
5.1

6

Issuer Data Critical Errors and Related Error Messages ............................................. 24
Appendix B—Required Fields........................................................................................... 29

1

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
1

Introduction

The Health Insurance Oversight System (HIOS) will allow the government to collect data from
states and individual and small group market issuers, which will be aggregated with other data
sources and made public on a consumer facing website. One initial mechanism for the Issuers to
submit their data is through the use of the HIOS Excel template.
These technical instructions explain the special features and other technical aspects related to the
use of the HIOS Issuer Data Submission Excel Tool. The Issuer Data Submission Excel Tool
consists of three worksheets that must be completed prior to submission:
Issuer General Info: Supports the data entry for the Issuer‘s corporate information,
corporate address, contact information, and ratings.
Product Info: Supports the data entry for all product offerings on behalf of the issuers.
The data to be entered includes Product Name, Enrollment Number, Product Type,
Product Enrollment, Individual or Small Group, Website Addresses, Geographic
Coverage and whether the product has been opted out of Phase 2.
Geographic Coverage: Supports the data entry for specific zip codes when a product is
not offered throughout the state.
The Office of Consumer Information & Insurance Oversight (OCIIO), a division of HHS,
recommends that users read this document thoroughly before using the tool.
The Issuer Data Submission Tool employs two versions of the workbook that serve different
purposes throughout the process:
Working files – are read-write enabled files that allow users to enter data in specified
input fields. Users may edit, save, name, and re-name working versions of these files.
Finalized files – are read-only files created by a process called finalization, which
modifies the format of working files to prepare them for submission to HHS. Finalization
converts formula results in calculated fields into hard-coded values, removes formulas
and other extraneous elements, and saves the file using a standard naming convention.
All macro file functionalities are disabled. The finalized save process will delete or
standardize some of the data in the finalized file according to specified business rules.
The finalized save process will not change the data in the working file. (See Section
2.4.1.)

2

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
2
Issuer Data Entry Form Functions
2.1
2.1.1

Submission Requirements
Initial Configuration Requirement

For the Issuer Data Entry Form to work properly, you must configure your PC to satisfy the
following requirements:
Have Microsoft Excel 2003 or greater installed.
Enable your Excel Standard Toolbar.
For Excel 2003, set your Excel Macro Security Settings to Medium (recommended). You
can do this by selecting Tools, Macro, and the Security command from your Excel menu
bar.*
For Excel 2007, set your Excel Macro Security Settings to ―Disable all macros with
notifications.‖ You can do this by selecting the Office Button, Excel Options, Trust
Center, Trust Center Settings, Macro Settings.*
* Macros from un-trusted sources could potentially contain malicious code. If your macro
settings are configured as stated above, you will be able to select which macros are safe and
therefore should be enabled. The following section details how to enable these macros so that
you can successfully use the Issuer Form.
2.2
2.2.1

How to Open the Issuer Form Workbook
Macro Security Level Setting

The Issuer Data Entry form uses macros to perform the built-in functions including the validation
and finalization processes.
For Excel 2003
If your Macro Security setting is set to Medium (as recommended above), you will be prompted
to enable or disable macros when you open the workbook. You must choose ―Enable macros‖ to
correctly open the form. If you choose ―Disable macros,‖ the workbook will display a screen
stating that the macros must be enabled in order to use the form. You will have to close and then
reopen the workbook to enable macros.
If your macro settings are set to High or Very High, the workbook will not be functional. A static
screen will display indicating that you must update your macro settings.
For Excel 2007
The Issuer Data Entry form is supported in Excel 2007. You will be able to open and work with
the HIOS files using Excel 2007‘s compatibility mode. However, you MUST save and/or

3

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
finalize the HIOS files in the Excel 2003 (.xls) format once you are finished. You will only be
able to submit an ―.xls‖ HIOS file.
If you try to save in Excel 2007 (.xlsx) you will get the following error. If you select yes it will
make the workbook inoperable.

Note: If a HIOS file is saved in the Excel 2007 (.xlsm) format, an error will occur when you
attempt to reopen the file. Please do not save the HIOS file in .xlsm format. This applies to
working files as well.
If your Macro Security setting is set to ―Disable all macros with notification‖ (as recommended
above), the workbook will open and you will see the following message above the worksheet:
―Security Warnings. Macros have been disabled.‖ – followed by an ‗Options...‘ button (as seen
in the screen shot below). You must click the ‗Options…‘ button and select ―Enable this
content‖ to continue working with the file.

4

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions

If the current security setting is set to ―Disable all macros except digitally signed macros‖ or
―Disable all macros without notifications‖, the workbook will not be functional (i.e. you will not
be able to validate and finalize).
You will be able to open and work with multiple Issuer Data Entry Forms in the same Excel
2007 instance. But if you want to close a file and open another one, you should also close Excel
and open the next file in a new instance of Excel. There are known issues related to Excel
2007 when you open a file in an existing Excel instance. If you encounter any issues when
opening a Issuer Data Entry form, then close Excel, go to Task Manager, delete any other Excel
processes, then open a new Issuer Data Entry file in a new Excel window.
Note: After completion of the workbook it is suggested that if you had to change your default
macro settings for this workbook that you change them back to your original macro settings.
2.2.2

Commands

Upon successfully opening a working Issuer Data Entry Form with the macros enabled, specific
buttons will appear in each worksheet.
Button names (from left to right):

5

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Validate (Shortcut: CTRL + shift + V) – this will perform the red circle and critical
validations.
Validate and Finalize (Shortcut: CTRL + shift + S) - this function will perform the
critical validations and create the read-only finalized file.
Format and Print Preview (Shortcut: CTRL + shift + P) – this function will format the
workbook for printing and bring to focus the print preview screen for the user.
2.2.3

Validations

All data entry fields are highlighted in green. All cells are formatted in Text format.
It is recommended that you not use Excel‘s ―Cut‖ function in the Issuer Data Entry Form
workbook as it will remove the cell‘s predefined formatting.
Note: You should use the Delete key instead of the Space bar to delete cell values from a cell.
2.2.4

Critical Validations

The green-highlighted data entry fields fall into three categories with respect to cell validation:
validation fields, critical validation fields, and non-validation fields. Validation fields have cellspecific rules regarding the types and format of data that can be entered into them. These rules
appear in message boxes called cell labels which are shown when the cell is highlighted.
2.3

Circle Invalid

Upon opening the worksheet, red circles will surround cells that do not pass specific validation
rules. Once the data has been entered and has passed the validation rules, the red circles will
disappear.
It is strongly recommended that you correct all red circle validations prior to finalizing your
workbook. Not all red circle validations are checked in the finalization process thus it is possible
that you finalize your file with data errors. These data errors will be caught when the file is being
processed and will be returned to you for correction.
It is highly recommended that you use the “Validate” function prior to the “Validate and
Finalize” function. You should only trigger the “Validate and Finalize” if no red circle
validations have failed.
Note: If you enter invalid information into a cell with validation rules and attempt to exit the cell,
you will receive the error below. Please click the ‗Cancel‘ button to continue working and
enter/select a valid value for the cell.

6

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions

2.3.1

Printing the file

You have two options for printing the Issuer Data Entry form. You may use the standard excel
Print function to print or you may click on the Format & Print Preview button within each
worksheet.
2.3.2

Save Functions

There are two save processes available within the Issuer data Entry form: a non-finalized and a
finalized save.
A non-finalized save can be invoked by clicking on the Excel Save icon on the Excel Standard
Toolbar or by selecting File and then Save from the Excel menu. This save process will save any
changes you have made to the workbook.
A finalized save occurs when the ―Finalize and Validate‖ function is invoked. (See Section 2.4,
―Finalizing the Issuer Data Entry Form for Submission.‖) This step is taken when the workbook
is complete and ready for submission.
Note: after clicking ―Save‖ or ―Finalize & Validate Form,‖ the following message may appear:

7

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Uncheck the box ―Check compatibility when saving this workbook‖ and click ―Continue.‖ The
popup will not appear next time you save a form.
2.4

Finalizing the Issuer Data Entry Form for Submission

To trigger the finalization process, click on the Validate & Finalize File button. The system will
perform all of the critical validations. The system will perform the finalization process and create
the read only file if all critical validations are passed.
As stated in Section 2.3, it is highly recommended that you correct all red circle validations
prior to triggering the finalization process.
2.4.1

Finalization Process

When the finalization function is triggered, the system will perform the following actions:
Check the required fields— Pre-determined fields (e.g., ―Issuer‖) must be entered for the
finalization to be successful; these fields are listed in Appendix B (―Required Fields‖).
Check the critical validations—Critical validation fields must comply with their
validation rules for the finalization process to be successful; the fields and business rules
are listed in Appendix A (―Critical Validation Fields‖). All critical errors must be
corrected for a file to be successfully finalized.
Create a finalized file with the following features:
All worksheets will display as read only in the finalized file.
Red circles will display for cells that do not pass validation rules. Note: You will
be able to finalize the file if the red circled cell is not one of the critical errors. A
list of critical validations for is included in Appendix A, ―Critical Errors.‖
2.4.2

File Naming Convention

Finalized Issuer Data Entry Submission workbooks are saved using the following standardized
naming convention:
Final____.xls
Use of this naming convention is a requirement for a successful submission. If you modify
the name of the finalized file, it will not be processed.
Example: Final_BlueCross_VA_20100427130426_Issuer Data Entry Form.xls
Note: Finalized files will be saved in the same directory where the working file is located.
Note: Original name refers to the name of the working file. There is not a requirement for how
the working file must be named and it will not impact the processing of the finalized file.

8

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
The working file name can be changed at any time. The finalized forms are read-only files. If
you need to make additional changes prior to submission, modify the working file and finalize
the file again. Remember that the naming convention mentioned above, which is used for the
finalized file, is required for submission.
2.5

Closing the file

When closing the file it is highly recommended that you save all current changes.
2.6

Password Protection

The Issuer Data Entry form is password protected. You may not modify the structure of the
workbook or worksheets. Each data item must be located in its pre-defined cell location for
successful processing.

9

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
3
Tips for Entering Data
Note: Please verify that all of the Submission Requirements specified in section 2.1 have been
met before continuing with this section.
This section will provide information on how to enter data into each required cell within the
workbook.
3.1

Issuer General Info Tab

The following table includes the cell location for each data entry field on the Issuer General Info
tab along with appropriate values.
Cell Location

Data

C3

Issuer Name

C4

Federal EIN

C5

NAIC Company Code

E3

State

E4

Market Coverage

E5

NAIC Group Code

G3

Issuer ID

C8
C9

Address Line 1
Address line 2

C10
C11

City
State

C12
E12

Zipcode
4 digit

Valid Values
Enter Issuers name. The system
allows up to 40 characters.
A 9 – digit number must be
entered. Do not enter a hyphen.
Enter the issuers NAIC company
code which should be a number
that is less than 6 digits. THIS
FIELD IS OPTIONAL.
Select a State from the drop
down. Do not type in a value.
Select Individual, Small
Group, or both from the drop
down. Do not type in a value.
Enter the issuer‘s 6 digit NAIC
Group Number. THIS FIELD IS
OPTIONAL.
THIS IS A READ ONLY
FIELD AND SHOULD NOT
BE USED.
Enter the address for the Issuer
Enter the rest of the address for
the issuer THIS FIELD IS
OPTIONAL.
Enter the city name for the Issuer
Select a State from the drop
down. Do not type in a value.
Enter a 5 digit Zipcode
Enter a 4 digit Zipcode. THIS
FIELD IS OPTIONAL.

10

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C15

Data
Individual Market

C16

Website address for Individual
Market website

C17

Small Group Market

C18

Website address for Small
Group Market website

C21

Customer Service Local
Number

C22

Customer ServiceToll Free
number

C23

Customer TTY

C24

Customer Website Address

E21

Customer Service Phone
Number Extension

C28

Primary Data Submission
Contact – Individual Market

Valid Values
Select Yes or No from the drop
down menu. Do not type in a
value
Enter the website address for the
Individual Market. This field is
only required if you select ‗Yes‘
to the Individual Market question
in cell C15.
Select Yes or No from the drop
down menu. Do not type in a
value
Enter the website address for the
Small Group Market. This field
is only required if you select
‗Yes‘ to the ‗Small Group
Market‘ question in cell C17.
Please enter the local phone
number. The system allows up to
15 characters.
Please enter the toll free number.
THIS FIELD IS OPTIONAL.
The system allows up to 15
characters.
Enter the phone number for the
TTY. The system allows up to 15
characters.
Enter a valid website address for
the customer service.
Enter the phone number
extension for customer service.
The system allows up to 6
characters. THIS FIELD IS
OPTIONAL.
Enter the first name of the
individual who is the primary
contact for the Individual Market
data submission. The system
allows up to 40 characters. This
field is only required if you
selected Individual or Both in the
Market Coverage cell (E4).

11

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C29

Data
Primary Data Submission
Contact Phone Number –
Individual Market

C30

Primary Data Submission
Contact Email Address –
Individual Market

E28

Primary Data Submission
Contact Last Name –
Individual Market

E29

Primary Data Submission
Contact Phone Number
Extension – Individual Market

C33

Back Up Data Submission
Contact First Name –
Individual Market

Valid Values
Enter the phone number of the
individual who is the primary
contact for the Individual Market
data submission. This field is
only required if you selected
Individual or Both in the Market
Coverage cell (E4).The system
allows up to 15 characters.
Enter the email address of the
individual who is the primary
contact for the Individual Market
data submission. This field is
only required if you selected
Individual or Both in the Market
Coverage cell (E4).Please make
sure to include the full email
address (e.g. [email protected]). The
system will validate that the @ is
part of the address.
Enter the last name of the
individual who is the primary
contact for the Individual Market
data submission.The system
allows up to 40 characters. This
field is only required if you
selected Individual or Both in the
Market Coverage cell (E4).
Enter the phone numebr
extension of the individual who
is the primary contact for the
Individual Market data
submission. The system allows
up to 15 characters. THIS FIELD
IS OPTIONAL.
Enter the first name of the
individual who is the backup
contact for the Individual Market
data submission.The system
allows up to 40 characters. This
field is optional.

12

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C34

Data
Back Up Data Submission
Contact Phone Number –
Individual Market

C35

Back Up Data Submission
Contact Email Address –
Individual Market

E33

Back Up Data Submission
Contact Last Name –
Individual Market

E34

Back Up Data Submission
Contact Phone Number
Extension – Individual Market

C39

Primary Issuer Data
Validation Contact –
Individual Market

Valid Values
Enter the phone number of the
individual who is the backup
contact for the Individual Market
data submission. The system
allows up to 15 characters. This
field is optional.
Enter the email address of the
individual who is the backup
contact for the Individual Market
data submission. This field is
optional.Please make sure to
include the full email address
(e.g. [email protected]). The system
will validate that the @ is part of
the address.
Enter the last name of the
individual who is the backup
contact for the Individual Market
data submission.The system
allows up to 40 characters. This
field is optional.
Enter the phone number
extension of the individual who
is the backup contact for the
Individual Market data
submission. The system allows
up to 15 characters. THIS FIELD
IS OPTIONAL.
Enter the first name of the
individual who is the primary
contact for the validation of
Individual Market data. The
system allows up to 40
characters. This field is only
required if you selected
Individual or Both in the Market
Coverage cell (E4).

13

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C40

Data
Primary Issuer Data
Validation Contact Phone
Number – Individual Market

C41

Primary Issuer Data
Validation Contact Email
Address – Individual Market

E39

Primary Issuer Data
Validation Contact Last Name
– Individual Market

E40

Primary Issuer Data
Validation Contact Phone
Number Extension –
Individual Market

C44

Back Up Issuer Data
Validation Contact First Name
– Individual Market

Valid Values
Enter the phone number of the
individual who is the primary
contact for the validation of
Individual Market data. The
system allows up to 15
characters. This field is only
required if you selected
Individual or Both in the Market
Coverage cell (E4).
Enter the email address of the
individual who is the primary
contact for the validation of
Individual Market data. Please
make sure to include the full
email address (e.g.
[email protected]). The system will
validate that the @ is part of the
address. This field is only
required if you selected
Individual or Both in the Market
Coverage cell (E4).
Enter the last name of the
individual who is the primary
contact for the validation of
Individual Market data. The
system allows up to 40
characters. This field is only
required if you selected
Individual or Both in the Market
Coverage cell (E4).
Enter the phone number
extension of the individual who
is the primary contact for the
validation of Individual Market
data. The system allows up to 15
characters. THIS FIELD IS
OPTIONAL.
Enter the first name of the
individual who is the back up
contact for the validation of
Individual Market data. The
system allows up to 40
characters.

14

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C45

Data
Back Up Issuer Data
Validation Contact Phone
Number – Individual Market

C46

Back Up Issuer Data
Validation Contact Email
Address – Individual Market

E44

Back Up Issuer Data
Validation Contact Last Name
– Individual Market

E45

Back Up Issuer Data
Validation Contact Phone
Number Extension –
Individual Market

C50

Primary Data Submission
Contact First Name – Small
Group Market

Valid Values
Enter the phone number of the
individual who is the back up
contact for the validation of
Individual Market data. The
system allows up to 15
characters.
Enter the email address of the
individual who is the back up
contact for the validation of
Individual Market data. Please
make sure to include the full
email address (e.g.
[email protected]). The system will
validate that the @ is part of the
address.
Enter the last name of the
individual who is the back up
contact for the validation of
Individual Market data. The
system allows up to 40
characters.
Enter the phone number
extension of the individual who
is the back up contact for the
validation of Individual Market
data. The system allows up to 15
characters. THIS FIELD IS
OPTIONAL.
Enter the first name of the
individual who is the primary
contact for the Small Group
Market data submission. The
system allows up to 40
characters. This field is only
required if you selected Small
Group or Both in the Market
Coverage cell (E4).

15

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C51

Data
Primary Data Submission
Contact Phone Number –
Small Group Market

C52

Primary Data Submission
Contact Email Address –
Small Group Market

E50

Primary Data Submission
Contact Last Name – Small
Group Market

E51

Primary Data Submission
Contact Phone Number
Extension – Small Group
Market

C55

Back Up Data Submission
Contact First Name – Small
Group Market

Valid Values
Enter the phone number of the
individual who is the primary
contact for the Small Group
Market data submission. This
field is only required if you
selected Small Group or Both in
the Market Coverage cell
(E4).The system allows up to 15
characters.
Enter the email address of the
individual who is the primary
contact for the Small Group
Market data submission. This
field is only required if you
selected Small Group or Both in
the Market Coverage cell
(E4).Please make sure to include
the full email address (e.g.
[email protected]). The system will
validate that the @ is part of the
address.
Enter the last name of the
individual who is the primary
contact for the Small Group
Market data submission.The
system allows up to 40
characters. This field is only
required if you selected Small
Group or Both in the Market
Coverage cell (E4).
Enter the phone numebr
extension of the individual who
is the primary contact for the
Small Group Market data
submission. The system allows
up to 15 characters. THIS FIELD
IS OPTIONAL.
Enter the first name of the
individual who is the backup
contact for the Small Group
Market data submission.The
system allows up to 40
characters. This field is optional.

16

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C56

Data
Back Up Data Submission
Contact Phone Number –
Small Group Market

C57

Back Up Data Submission
Contact Email Address –
Small Group Market

E55

Back Up Data Submission
Contact Last Name – Small
Group Market

E56

Back Up Data Submission
Contact Phone Number
Extension – Small Group
Market

C61

Primary Issuer Data
Submission Contact – Small
Group Market

Valid Values
Enter the phone number of the
individual who is the backup
contact for the Small Group
Market data submission. The
system allows up to 15
characters. This field is optional.
Enter the email address of the
individual who is the backup
contact for the Small Group
Market data submission. This
field is optional.Please make sure
to include the full email address
(e.g. [email protected]). The system
will validate that the @ is part of
the address.
Enter the last name of the
individual who is the backup
contact for the Small Group
Market data submission.The
system allows up to 40
characters. This field is optional.
Enter the phone number
extension of the individual who
is the backup contact for the
Small Group Market data
submission. The system allows
up to 15 characters. THIS FIELD
IS OPTIONAL.
Enter the first name of the
individual who is the primary
contact for the validation of
Small Group Market data. The
system allows up to 40
characters. This field is only
required if you selected Small
Group or Both in the Market
Coverage cell (E4).

17

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
C62

Data
Primary Issuer Data
Submission Contact Phone
Number – Small Group
Market

C63

Primary Issuer Data
Submission Contact Email
Address – Small Group
Market

E61

Primary Issuer Data
Submission Contact Last
Name – Small Group Market

E62

Primary Issuer Data
Submission Contact Phone
Number Extension – Small
Group Market

C66

Back Up Issuer Data
Submission Contact First
Name – Small Group Market

Valid Values
Enter the phone number of the
individual who is the primary
contact for the validation of
Small Group Market data. The
system allows up to 15
characters. This field is only
required if you selected Small
Group or Both in the Market
Coverage cell (E4).
Enter the email address of the
individual who is the primary
contact for the validation of
Small Group Market data. Please
make sure to include the full
email address (e.g.
[email protected]). The system will
validate that the @ is part of the
address. This field is only
required if you selected Small
Group or Both in the Market
Coverage cell (E4).
Enter the last name of the
individual who is the primary
contact for the validation of
Small Group Market data. The
system allows up to 40
characters. This field is only
required if you selected Small
Group or Both in the Market
Coverage cell (E4).
Enter the phone number
extension of the individual who
is the primary contact for the
validation of Small Group
Market data. The system allows
up to 15 characters. THIS FIELD
IS OPTIONAL.
Enter the first name of the
individual who is the back up
contact for the validation of
Small Group Market data. The
system allows up to 40
characters. This field is optional.

18

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location

Data
Back Up Issuer Data
Submission Contact Phone
Number – Small Group
Market

Valid Values
C67
Enter the phone number of the
individual who is the back up
contact for the validation of
Small Group Market data. The
system allows up to 15
characters. This field is optional.
C68
Back Up Issuer Data
Enter the email address of the
Submission Contact Email
individual who is the back up
Address – Small Group
contact for the validation of
Market
Small Group Market data. Please
make sure to include the full
email address (e.g.
[email protected]). The system will
validate that the @ is part of the
address. This field is optional.
E66
Back Up Issuer Data
Enter the last name of the
Submission Contact Last
individual who is the back up
Name – Small Group Market
contact for the validation of
Small Group Market data. The
system allows up to 40
characters. This field is optional.
E67
Back Up Issuer Data
Enter the phone number
Submission Contact Phone
extension of the individual who
Number Extension – Small
is the back up contact for the
Group Market
validation of Small Group
Market data. The system allows
up to 15 characters. This field is
optional.
C71
Individual Market Issuer rated Select Yes or No from the drop
by any rating company
down menu. Do not type in a
value.
You may enter up to 5 rows of rating information. The system will verify that at least one set
of rating data is entered if you Select „Yes‟ to having been rated by a rating company.
B73
Individual Market Rating
Select rating company from the
Company
drop down menu. Do not type
in a value. This field is only
required if you selected Yes in
cell C71 (rated by a rating
company).
C73 Individual Market Rating type Select rating from the drop
down menu. Do not type in a
value. This field is only required
if you selected Yes in cell C71
(rated by a rating company).

19

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
D73

Data
Individual Market Rating

E73

Individual Market Rating
Company Other

E74

Individual Market Rating
Type Other

C80

Small Group Market rated by
and rating company

B82

Small Group Market Rating
Company

C82

Small Group Market Rating
type

D82

Small Group Market Rating
Company Other

E82

Small Group Market Rating
Type Other

3.2

Valid Values
Enter Rating. This field is only
required if you selected Yes in
cell C71 (rated by a rating
company).
Enter the rating company if you
selected ‗Other/Describe‘ in cell
B73.
Enter the rating type if you
selected ‗Other/Describe‘ in cell
C73.
Select rating company from the
drop down menu. Do not type
in a value.
Select rating from the drop
down menu. Do not type in a
value. This field is only required
if you selected Yes in cell C80.
(rated by a rating company).
Enter Rating. This field is only
required if you selected Yes in
cell C80 (rated by a rating
company).
Enter the rating company if you
selected ‗Other/Describe‘ in cell
B82.
Enter the rating type if you
selected ‗Other/Describe‘ in cell
C82.

Product Info Tab

The following table includes the cell location for each data entry field on the Product Info tab,
along with appropriate values.

C3
D3

Cell Location

Data
Product Name
Enrollment code/Group
Number

E3

Product Type

Valid Valies
Enter a product name
Enter the Enrollment code/Group
Number given internally to this
product .THIS FIELD IS
OPTIONAL.
Select product type from the
drop down menu. Do not type
in a value.

20

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
Cell Location
F3

Data
Other Product Type
Description

G3
H3

Product Enrollment
Individual or Small Group

I3

Website address (Benefit at a
Glance)
Website address (Formulary)

J3
K3
L3

Website address (Provider
Network)
Covers whole state

M3

Opt-out of phase 2

3.3

Valid Valies
Enter a product description. This
field is only required if you
selected ‗Other/Describe‘ for the
product type.
Enter the number of enrollment
Select Individual or Small
Group from the drop down list.
Do not type in a value
Enter a website address for the
benefit description.
Enter a website address. THIS
FIELD IS OPTIONAL.
Enter a website address.
Select Yes or No from the drop
down menu. Do not type in a
value
Select Yes or No from the drop
down menu. Enter yes if you
are no longer excepting
enrollment in this product. Do
not type in a value

Geographic Coverage

The following table includes the cell location for each data entry field on the Geographic
Coverage tab, along with appropriate values. You are only required to enter data in this tab if at
least one of your products is not offered in the whole state.
Cell Location
B3

Data
Cross reference

C3

Zip Code

Valid Valies
Enter the cross reference from
Worksheet 2 Column A that
indicates the plan you are
entering geographic information
for.
Enter a Zip Code.

Please follow the following guidelines when entering data into this worksheet:
1) Do not skip any rows when entering data. The system will interpret an empty row as
the end of the data set (i.e. any data after a blank row will not be processed)

21

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
2) If you enter a product in a row on the Product Info worksheet, you must enter all
the required data for that product. Do not leave any of the required cells blank. All
cells on that row are required with the exception of Column D (Enrollment
Code/Group Number), Column F (Other Product Type Description – this field is
only required if you select Other as the product type), and Column J (Formulary
Website Address).
3) Any data entered within a row that does not have a corresponding Product Name
will not be processed.
4) If you specify that a product is not offered throughout the state, you MUST specify
which zip codes the product is offered in. In the Geographic Coverage tab, enter one
row for every zip code each product is offered in. Use the Cross Reference to make
sure you are referencing the correct product. You must enter at least one zip code if
the product is only offered in a subset of the state.
Note – You DO NOT need to enter zip codes if you indicate that the product is
offered in the whole state.

22

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
4
Troubleshooting and FAQ
4.1

FAQ’s

Question 1: I am trying to open Issuer Data Entry files in Excel 2007, but I do not see the Option
button. How do I enable contents?
1.

Click the Microsoft Office Button

, and then click Excel Options.

2.

Click Trust Center, click Trust Center Settings, and then click Message Bar.

3.

Click the first radio button below:
Show the Message Bar in all applications when document content has been blocked. This
option is selected by default so that you get Message Bar alerts whenever potentially
unsafe content has been disabled. The option is not selected if you clicked the Disable all
macros without notification option on the Macros pane of the Trust Center. If you click
Disable all macros without notification, you won't get Message Bar alerts when macros
are disabled.
Never show information about blocked content. this option disables the Message Bar.

4.2

Submission Information

Please submit your completed FINALIZED files via email to [email protected] .
Please remember not to change the name of the system-generated finalized file.
4.3

Support

HIOS Help
If you need additional assistance, please call the HIOS Help Desk at 1-877-343-6507 or email
them at [email protected].
.

23

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
5
Appendix A— Critical Errors
5.1

Issuer Data Critical Errors and Related Error Messages

These errors will prevent successful finalization.
1. Issuer General Info, C3, Issuer Name
a. Display error message if cell is blank.
b. Error message: An issuer name must be entered in Worksheet 1 C3
2. Issuer General Info, C4, Federal EIN
a. Display error message if cell is blank.
b. Error message: A federal EIN number must be entered in Worksheet 1 C4
3. Issuer General Info, C8, Address Line 1
a. Display error message if cell is blank.
b. Error message: An address must be entered in Worksheet 1 C8
4. Issuer General Info, C10, Issuer City
a. Display error message if cell is blank.
b. Error message: A city name less than 50 characters must be entered in worksheet
1 C10
5. Issuer General Info, C11, Issuer Issuer
a. Display error message if the value entered is not one of the Issuers in the list.
b. Error message: A Issuer must be selected from the drop down menu in Worksheet
1 C11
6. Issuer General Info, C12, Issuer Zipcode
a. Display error message if cell is blank.
b. Error message: A Zip Code must be entered in Worksheet 1 C12
7. Issuer General Info, C15, Individual Market
a. Display error message if the value entered is not one of the options in the drop
down.
b. Error Message: A value must be selected from the drop down menu in Worksheet
1 C15.
8. Issuer General Info, C16, Website for Individual Market
a. Display error message if this cell is blank and Yes is entered in C15.
b. Error Message: A valid Website must be entered in Worksheet 1 C16 if individual
market is offered.
9. Issuer General Info, C17, Small Group Market
a. Display error message if the value entered is not one of the options in the drop
down
b. Error Message: A value must be selected from the drop down menu in Worksheet
1 C17.
10. Issuer General Info, C18, Website for Small group market
a. Display error message if this cell is blank and Yes is entered in C17.
b. Error Message:A valid Website must be entered in Worksheet 1 C18 if Small
Group is offered.
11. Issuer General Info, C21, Customer Service Local number
a. Display error message if cell is blank.

24

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
b. Error message: A Customer Service Local Number must be entered in Worksheet
1 C21
12. Issuer General Info, C24, Customer Service Website Address
a. Display error message if cell is blank.
b. Error Message:A valid Website must be entered in Worksheet 1 C24
13. Issuer General Info,C28, Data Submission/Individual Market Primary Contact Name
a. Display error message if cell is blank.
b. Error message: A contact name must be entered in Worksheet 1 C28
14. Issuer General Info, C29, Data Submission/Individual Market Primary Contact Phone
Number
a. Display error message if cell is blank.
b. Error message: A Phone Number must be entered in Worksheet 1 C29
15. Issuer General Info, C30, Data Submission/Individual Market Primary Contact email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C30
16. Issuer General Info, C39, Issuer Data Submission Validation/Individual Market Primary
Contact Name
a. Display error message if cell is blank.
b. Error message: A Issuer Data Submission Validation contact name must be
entered in Worksheet 1 C39
17. Issuer General Info,C40, Issuer Data Submission Validation/Individual Market Primary
Phone Number
a. Display error message if cell is blank.
b. Error message: A Phone Number must be entered in Worksheet 1 C41
18. Issuer General Info,C41, Issuer Data Submission Validation/Individual Market Primary
email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C42
19. Issuer General Info,C50, Data Submission/Small Group Market Primary Contact Name
a. Display error message if cell is blank.
b. Error message: A contact name must be entered in Worksheet 1 C50
20. Issuer General Info, C51, Data Submission/Small Group Market Primary Phone Number
a. Display error message if cell is blank
b. Error message: A Phone Number must be entered in Worksheet 1 C51
21. Issuer General Info, C52, Data Submission/Small Group Market Primary Email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C52
22. Issuer General Info, C61,Issuer Data Submission Validation Contact/Small Group Market
Primary Contact Name
a. Display error message if cell is blank.
b. Error message: A contact name must be entered in Worksheet 1 C61
23. Issuer General Info,C62, Issuer Data Submission Validation Contact/Small Group Market
Primary Phone Number
a. Display error message if cell is blank.
b. Error message: A Phone Number must be entered in Worksheet 1 C62

25

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
24. Issuer General Info, C63, Issuer Data Submission Validation Contact/Small Group
Market Primary Email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C63
25. Issuer General Info, C71,Issuer Rating for Individual Market
a. Display error message if the value entered is not one of the options in the drop
down.
b. Error Message: Yes or No must be selected from the drop down menu in
Worksheet 1 C71.
26. Issuer General Info, B73, Rating Company
a. Display error message if this field is blank, and Yes is entered in W3 C71.
b. Error message: A rating company must be entered in Worksheet 1 B73 if the
company has been rated in the past 2 years.
27. Issuer General Info,C73, Rating type
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B73.
b. Error message: A rating type must be entered in Worksheet 1 C73 if Rating
company is entered.
28. Issuer General Info, D73, Rating
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B73.
b. Error message: A rating must be entered in Worksheet 1 D73 if Rating company
is entered in W3 B73.
29. Issuer General Info, C80,Issuer Rating for Small Group Market
a. Display error message if the value entered is not one of the options in the drop
down.
b. Error Message: Yes or No must be selected from the drop down menu in
Worksheet 1 C80.
30. Issuer General Info,B82, Rating Company
a. Display error message if this field is blank, and Yes is entered in Worksheet 1
C80.
b. Error message: A rating company must be entered in Worksheet 1 B82 if the
company has been rated in the past 2 years.
31. Issuer General Info, C82, Rating type
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B82.
b. Error message: A rating type must be entered in Worksheet 1 C82 if Rating
company is entered.
32. Issuer General Info, D82, Rating
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B80.
b. Error message: A rating must be entered in Worksheet 1 D73 if Rating company
is entered in W3 D82.
33. Issuer General Info, E3, Issuer
a. Display error message if this field is blank.
b. Error message: A Issuer must be seleted on Worksheet 1 E3.

26

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
34. Issuer General Info, E4, Market Coverage
a. Display error message if this field is blank.
b. Error message: A Market coverage value must be seleted on Worksheet 1 E4.
35. Product Info,C3,Product Name
a. Display error message if cell is blank
b. Error message: A Product Name must be entered in Worksheet 2 C3.
36. Product Info,E3, Product Type
a. Display error message if the this field is blank.
a. Error Message: A value must be entered in Worksheet 2 E3.
37. Product Info, F3, Other Product Type description
a. Display error message if this field is blank and Other is selected in cell E3
Product Type.
b. Error Message: Other Product Type description is required if Other produdct type
is selected.
38. Product Info,G3, Product Enrollment
a. Display error message if cell is blank
b. Error message: Product Enrollment must be entered in Worksheet 2 F3.
39. Product Info,H3, Individual/small Group
a. Display error message if blank.
b. Error Message: A value must be selected from the drop down menu in Worksheet
2 G3.
40. Product Info,J3, Website Address Benefit
a. Display error message if cell is blank.
b. Error message: Product information worksheet Website Address Benefit in
Worksheet 2 H3 is required.
41. Product Info,K3, Website Address Provider Network
a. Display error message if cell is blank
b. Error message: Product Information worksheet Website Address for Provider
Network in Worksheet 2 J3 is required.
42. Product Info,L3, Covers Whole Issuer
a. Display error message if this field is blank.
b. Error Message: A value must be selected from the drop down menu in Worksheet
2 K3.
43. Product Info,M3, Short Term Limited
a. Display error message if this field is blank.
b. Error Message: A value must be selected from the drop down menu in Worksheet
2 L3.
44. Geographic Coverage,B Column , Cross Reference
a. Display error message if a Product does not cover a whole Issuer, there must be a
matching Cross Reference number found in this column for that Product.
b. Error message: A matching cross reference must be entered in Worksheet 3 B3 a
product does not cover whole Issuer.
45. Geographic Coverage,C column, Zip Code
a. Display error message if a Cross Reference number is entered, this field is
required.

27

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
b. Error message: A Zip code must be entered in Worksheet 3 C3 if a cross reference
number is entered in column B.

28

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions
6

Appendix B—Required Fields
Issuer General Info worksheet:
Issuer Name
Federal EIN
State
Market Coverage
Address Line 1
City
State
Zip
Do you offer Individual Market?
 Individual website if Individual Market is covered
Do you offer Small Group Market?
 Small Group website if Small Group Market is covered
Local Number
Website Address
If Individual Market is covered, the following fields are required:
 Individual Primary Data Submission Contact First Name
 Individual Primary Data Submission Contact Last Name
 Individual Primary Data Submission Contact Phone Number
 Individual Primary Data Submission Contact Email Address
 Individual Primary Data Validation Contact First Name
 Individual Primary Data Validation Contact Last Name
 Individual Primary Data Validation Contact Phone Number
 Individual Primary Data Validation Contact Email Address
If Small Group Market is covered, the following fields are required:
 Small Group Primary Data Submission Contact First Name
 Small Group Primary Data Submission Contact Last Name
 Small Group Primary Data Submission Contact Phone Number
 Small Group Primary Data Submission Contact Email Address
 Small Group Primary Data Validation Contact First Name
 Small Group Primary Data Validation Contact Last Name
 Small Group Primary Data Validation Contact Phone Number
 Small Group Primary Data Validation Contact Email Address
Individual Market – Is the Issuer rated?
If the Individual Market offerings have been rated, the following fields are required:
 Rating Company
 Rating Type
 Rating
Small Group Market – Is the Issuer rated?
If the Small Group Market offerings have been rated, the following fields are
required:

29

Health Insurance Oversight System – Excel Data Submission Tool
Technical Instructions




Rating Company
Rating Type
Rating

Product Info Worksheet:
Product Name
Product Type
Product Enrollment
Individual or Small Group
Website address (Benefit at a Glance)
Website Address (Provider Network)
Covers whole State?
Opt out Phase 2?
Geographic Coverage Worksheet:
Cross Reference
Zip code

30

OMB Control Number: 0938-1086

Worksheet 1 - Issuer General Information
1. Corporate Information
Issuer Name:

State

Federal EIN:

Market Coverage

NAIC Company Code

NAIC Group Code

Issuer ID

2. Address
Address Line 1:
Address Line 2:
City:
State:
Zip:

4 digit

3. Individual and Small Group Market
1). Do you offer Individual market?
2). Enter the web address for the Individual
Market website if Yes is entered in C15.
3). Do you offer Small Group market?
4). Enter the web address for the Small Group
Market website if Yes is entered in C17.
4. Customer Service (Individual Market) or Employee Group (Small Group Market) Contact
Local Number:

Extension:

Toll Free Number
TTY:
Website Address
5. Data Submission Contact - Individual Market
Primary Contact
First Name:

Last Name:

Phone Number:
Email:

Extension:

Backup Contact
First Name:

Last Name:

Phone Number:
Email:

Extension:

6. Issuer Data Submission Validation Contact - Individual Market
Primary Contact
First Name:

Last Name:

Phone Number:
Email:

Extension:

Backup Contact
First Name:

Last Name:

Phone Number:
Email:

Extension:

7. Data Submission Contact - Small Group Market
Primary Contact
First Name:

Last Name:

Phone Number:
Email:

Extension:

Backup Contact
First Name:
Phone Number:
Email:

Last Name:
Extension:

8. Issuer Data Submission Validation Contact - Small Group Market
Primary Contact
First Name:
Phone Number:
Email:
Backup Contact
First Name:
Phone Number:
Email:

Last Name:
Extension:

Last Name:
Extension:

9. Ratings- Individual Market
Is Issuer rated by any rating company?
Rating Company (rated in the past 2 years)

Rating Type

Rating

Rating Company Other/Describe

Rating Type Other/Describe

Rating Type

Rating

Rating Company Other/Describe

Rating Type Other/Describe

10. Ratings- Small Group Market
Is Issuer rated by any rating company?
Rating Company (rated in the past 2 years)

PRA Disclosure Statement

According to the Paperwork Reduction Act of 1995, no persons are required to respond to a collection of information unless it displays a valid OM
control number for this information collection is 0938-1086. The time required to complete this information collection is estimated to average ( 30.32 hours)
(1,819.2 minutes) per response, including the time to review instructions, search existing data resources, gather the data needed, and complete and review
information collection. If you have comments concerning the accuracy of the time estimate(s) or suggestions for improving this form, please write to: CM
7500 Security Boulevard, Attn: PRA Reports Clearance Officer, Mail Stop C4-26-05, Baltimore, Maryland 21244-1850

Worksheet 2 - Product Information
Cross
Reference
1

Product ID

Product Name

Enrollment Code/Group
Number
Product Type

Other Product Type
Description

Product
Enrollment

Individual or
Small Group

Website Address (Benefit
at a Glance)

Website Address
(Formulary)

OMB Control Number: 0938-1086
Website Address
Covers Whole
Opt-out of
(Provider Network)
State?
Phase 2?

OMB Control Number: 0938-1086

Worksheet 3 - Geographic Coverage (Required if product does not cover whole State)
Cross Reference

Zip Code

OMB Control Number: 0938-1086

OMB Contr