Download:
pdf |
pdfFormatted: Left
OMB Control Number: 0938-1086
Health Insurance Oversight System (HIOS) Excel
Data Submission Tool - States
Technical Instructions
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 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 ( 10.17
hours) or (609.6 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.
Formatted: Left
OMB Control Number: 0938-1086
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 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 ( 10.17 hours) or (609.6 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 April 28May 5, 2010
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 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 ( 10.17
hours) or (609.6 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.
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Table of Contents
Formatted
...
Formatted
...
Formatted
...
Formatted
...
Field Code Changed
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Field Code Changed
...
1
Introduction ........................................................................................................................2
Formatted
...
2
State Data Entry Form Functions .......................................................................................3
Field Code Changed
...
Formatted
...
Formatted
...
Field Code Changed
...
2.1
2.2
OpeningHow to Open the State Data Entry Form Workbook ........................................3
2.3
Circle Invalid .................................................................................................................7
Formatted
...
2.4
Finalizing the State Data Entry Form for Submission ....................................................9
Field Code Changed
...
2.5
Closing the file ............................................................................................................10
Field Code Changed
...
Formatted
...
Field Code Changed
...
Formatted
...
2.6
3
Password Protection ...................................................................................................10
Troubleshooting and FAQTips for Entering Data ..............................................................11
3.1
FAQ’sState General Info Tab......................................................................................11
Field Code Changed
...
3.2
SupportState Issuer & Product Info Tab .....................................................................15
Formatted
...
Field Code Changed
Trouble
...
4
Appendix A— Critical Errors ...............................................................................................4
4.1
5
State Data Critical Errors and Related Error Messages ............................................. 4.1
Appendix B—Required Fields..........................................................................................4.2
4.3
5
Support .......................................................................................................................18
Appendix A— Critical Errors .............................................................................................19
5.1
6
InstallationSubmission Requirements ...........................................................................3
State Data Critical Errors and Related Error Messages .............................................. 19
Appendix B—Required Fields...........................................................................................22
1
Formatted
...
Field Code Changed
...
FAQ’s
Submis
Formatted
...
Field Code Changed
...
Formatted
...
Formatted
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Field Code Changed
...
Formatted
...
Field Code Changed
...
Field Code Changed
...
OMB Control Number: 0938-1086
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
1
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
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 states 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 State Data Submission Excel Tool. The State Data Submission Excel Tool
consists of two worksheets that must be completed prior to submission:
•
State General Info: Supports the data entry for the state’s general information, primary
and validation contacts.
•
State Issuer & Product Info: Supports the data entry for a listing of issuers with offerings
in the corresponding state, along with contact and product information for each issuer.
The Department of Health and Human ServicesThe Office of Consumer Information &
Insurance Oversight (OCIIO), a division of HHS, recommends that users read this
document thoroughly before using the tool.
The State 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
Formatted: Font: Bold
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
2
State Data Entry Form Functions
2.1
Installation
2.1
Submission Requirements
2.1.1
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
Initial Configuration Requirement
For the State 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. *
•
Enable macros when you open the State Data Collection File workbook.
Opening* 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 State Form.
2.2
2.2.1
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
How to Open the State Data Entry Form Workbook
Macro Security Level Setting
For Excel 2003
The State Data Entry form uses macros to perform the built-in functions including the validation
and finalization processes.
For security purposes, it is highly recommended that the Macro Security Level Setting be set at
Medium. You can do so by selecting Tools, Macro, and the Security command from the Excel
menu bar. You will not be able to open the State Data Entry Form if your macro security settings
are set to High or Very High.
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
3
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
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 State 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
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
Formatted: Font: Arial, 10 pt
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
5
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
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 State 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
State Data Entry form, then close Excel, go to Task Manager, delete any other Excel processes,
then open a new State 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
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Commands
Upon successfully opening a working State Data Entry Form with the macros enabled, specific
buttons will appear in theeach worksheet 1.
Button names (from left to right):
6
OMB Control Number: 0938-1086
Draft 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
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
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 State 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
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Circle Invalid
Upon triggeringopening the Validation process, aworksheet, red circle circles will surround cells
that do not pass specific validation rules. Once the data has been entered and has passed the
validation rules are corrected, the Circle Invalidred 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.
7
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
2.3.1
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
Printing the file
You musthave two options for printing the State Data Entry form. You may use the standard
excel Print function to print the State Data Entry Formor you may click on the Format & Print
Preview button within each worksheet.
2.3.2
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Save Functions
There are two save processes available within the State 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 State Data Entry Form for Submission.”) This step is taken when the workbook
is complete and ready for submission.
Note: after clicking “Save” or “Finalize and& Validate Form,” the following message may
appear:
8
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
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 State Data Entry Form for Submission
To trigger the finalization process, click on the Validate and& 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., “State”) 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
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
9
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
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 State 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_VA20100427130426_State 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.
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
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Password Protection
The State 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.
10
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
3
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
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
State General Info Tab
The following table includes the cell location for each data entry field on the State General Info
tab along with appropriate values.
Cell Location
Data
E3
State Name
E4
Underwriting Status
E6
Does your state have a
comparison tool for the Small
Group Market?
Website address for
comparative tool or state
address.
E7
E9
E10
Does your state have a
comparison tool for the
Individual Market?
Website address for
comparative tool or state
address.
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Valid Valies
Select a value from the drop
down list. Do not type in a state
name.
Select Yes or No from the drop
down. Do not type in a value.
Select Yes or No from the drop
down. Do not type in a value.
If you answered Yes to E6, enter
the full website address for the
comparative tool (e.g.
www.comparative_tool.gov) . If
you answered No to E6, enter
your full state website address
(e.g. www.state.us.gov).
Select Yes or No from the drop
down. Do not type in a value.
If you answered Yes to E9, enter
the full website address for the
comparative tool (e.g.
www.comparative_tool.gov) . If
you answered No to E9, enter
your full state website address
(e.g. www.state.us.gov).
11
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Cell Location
D14
Data
Primary State Data
Submission Contact First
Name
F14
Primary State Data
Submission Contact Last
Name
D15
Primary State Data
Submission Contact Phone
Number
F15
Primary State Data
Submission Contact Phone
Number Extension
D16
Primary State Data
Submission Contact Email
Address
D19
Back Up State Data
Submission Contact First
Name
F19
Back Up Data Submission
Contact Last Name
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Valid Valies
Enter the first name of the
individual who is the primary
contact for the submission of this
data. The system allows up to 40
characters.
Enter the last name of the
individual who is the primary
contact for the submission of this
data. The system allows up to 40
characters.
Enter the phone number for the
individual who is the primary
contact for the submission of this
data. The system allows up to 15
characters.
Enter the phone number
extension for the individual who
is the primary contact for the
submission of this data. The
system allows up to 15
characters. THIS FIELD IS
OPTIONAL.
Enter the email address for the
individual who is the primary
contact for the submission of this
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 first name of the
individual who is the back up
contact for the submission of this
data. The system allows up to 40
characters.
Enter the last name of the
individual who is the back up
contact for the submission of this
data. The system allows up to 40
characters.
12
Formatted: Font: Arial, 10 pt
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Cell Location
D20
Data
Back Up Data Submission
Contact Phone Number
F20
Back Up Data Submission
Contact Phone Number
Extension
D21
Back Up State Data
Submission Contact Email
Address
D25
Primary State Data Validation
Contact First Name
F25
Primary State Data Validation
Contact Last Name
D26
Primary State Data Validation
Contact Phone Number
F26
Primary State Data Validation
Contact Phone Number
Extension
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Valid Valies
Enter the phone number for the
individual who is the back up
contact for the submission of this
data. The system allows up to 15
characters.
Enter the phone number
extension for the individual who
is the back up contact for the
submission of this data. The
system allows up to 15
characters. THIS FIELD IS
OPTIONAL.
Enter the email address for the
individual who is the back up
contact for the submission of this
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 first name of the
individual who is the primary
contact for the validation of this
data. The system allows up to 40
characters.
Enter the last name of the
individual who is the primary
contact for the validation of this
data. The system allows up to 40
characters.
Enter the phone number for the
individual who is the primary
contact for the validation of this
data. The system allows up to 15
characters.
Enter the phone number
extension for the individual who
is the primary contact for the
validation of this data. The
system allows up to 15
characters. THIS FIELD IS
OPTIONAL.
13
Formatted: Font: Arial, 10 pt
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Cell Location
D27
Data
Primary State Data Validation
Contact Email Address
D30
Back Up State Data Validation
Contact First Name
F30
Back Up State Data Validation
Contact Last Name
D31
Back Up State Data Validation
Contact Phone Number
F31
Back Up State Data Validation
Contact Phone Number
Extension
D32
Back Up State Data Validation
Contact Email Address
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Valid Valies
Enter the email address for the
individual who is the primary
contact for the validation of this
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 first name of the
individual who is the back up
contact for the validation of this
data. The system allows up to 40
characters.
Enter the last name of the
individual who is the back up
contact for the validation of this
data. The system allows up to 40
characters.
Enter the phone number for the
individual who is the back up
contact for the validation of this
data. The system allows up to 15
characters.
Enter the phone number
extension for the individual who
is the back up contact for the
validation of this data. The
system allows up to 15
characters. THIS FIELD IS
OPTIONAL.
Enter the email address for the
individual who is the back up
contact for the validation of this
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.
14
Formatted: Font: Arial, 10 pt
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
3.2
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
State Issuer & Product Info Tab
The following table includes the cell location for each data entry field on the State Issuer &
Product Info tab, along with appropriate values.
On this worksheet you will include one row for each Issuer/Market with offerings within your
state. If an Issuer has offerings in both the Individual and Small Group markets, you must create
two rows fir this issuer.
Cell Location
Data
B5
Issuer Name
C5
Individual/Small Group
D5
Issuer Data Submission
Contact Name
E5
Issuer Data Submission
Contact Phone Number
F5
Issuer Data Submission
Contact Email Address
G5
Customer Service Contact
Phone Number for Consumers
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Valid Valies
Enter the name of the Insurance
Issuer.
Select Individual or Small
Group from the drop down. Do
not type in a value.
Enter the name of the individual
who is your point of contact at
the Issuer organization. The
system allows up to 40
characters.
Enter the phone number for the
individual who is your point of
contact at the issuer organization.
The system allows up to 15
characters.
Enter the email address for the
individual who is your point of
contact at the issuer organization.
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 phone number for the
individual who is your point of
contact at the Issuer
organization. The system allows
up to 15 characters. THIS FIELD
IS OPTIONAL.
15
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Cell Location
H5
I5
J5
K5
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Data
No. of products offered - Fee
for Service
Valid Valies
Enter the number of Fee for
Service products the
corresponding Issuer offers for
the corresponding market
(Individual or SmallGroup). The
number must be a whole number.
If the Issuer does not offer any
Fee for Service products, enter 0.
Do not leave this field blank.
No. of products offered Enter the number of HMO
HMO
products the corresponding
Issuer offers for the
corresponding market (Individual
or SmallGroup). The number
must be a whole number. If the
Issuer does not offer any HMO
products, enter 0. Do not leave
this field blank.
No. of products offered - PPO Enter the number of PPO
products the corresponding
Issuer offers for the
corresponding market (Individual
or SmallGroup). The number
must be a whole number. If the
Issuer does not offer any PPO
products, enter 0. Do not leave
this field blank.
No. of products offered - EPO Enter the number of EPO
products the corresponding
Issuer offers for the
corresponding market (Individual
or SmallGroup). The number
must be a whole number. If the
Issuer does not offer any EPO
products, enter 0. Do not leave
this field blank.
16
Formatted: Font: Arial, 10 pt
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Cell Location
L5
M5
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Data
Valid Valies
No. of products offered - POS Enter the number of POS
products the corresponding
Issuer offers for the
corresponding market (Individual
or SmallGroup). The number
must be a whole number. If the
Issuer does not offer any POS
products, enter 0. Do not leave
this field blank.
No. of products offered Enter the number of Other
Other
products the corresponding
Issuer offers for the
corresponding market (Individual
or SmallGroup). The number
must be a whole number. If the
Issuer does not offer any Other
products, enter 0. Do not leave
this field blank.
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)
2) If you enter an Issuer Name in a row, you must enter all the required data for that
Issuer. Do not leave any of the required cells blank. All cells on that row are
required with the exception G5, Customer Service Phone Number.
3) If the Issuer does not have any products of a specific type, please make sure to enter
a 0 (zero) into the corresponding cell.
4) Any data entered within a row that does not have a corresponding Issuer Name will
not be processed.
17
Formatted: Font: Arial, 10 pt
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
34
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
Troubleshooting and FAQ
3.14.1 FAQ’s
Question 1: I am trying to open State Data Entry files in Excel 2007, but I do not see the Option
button. How do I enable contents?
, and then click Excel Options.
1.
Click the Microsoft Office Button
2.
Click Trust Center, click Trust Center Settings, and then click Message Bar.
3.
Click the first radio button below:
•
•
4.2
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
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.
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.
3.24.3 Support
HIOS Help
If you need additional assistance, please call the HIOS Help Desk at 1-877-343-6507 (available
beginning on Friday, May 7 2010) or email them at at [email protected].
18
OMB Control Number: 0938-1086
Formatted: Font: Arial, 10 pt
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
45 Appendix A— Critical Errors
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
4.15.1 State Data Critical Errors and Related Error Messages
These errors will prevent successful finalization.
1. State Data General Info, E3, State Name
a. Display error message if this field is blank.
b. Error Message: State name at E3 required.
2. State Data General Info, E4, Underwriting Status
a. Display error message if this field is blank.
b. Error Message: A State must be selected from the drop down menu in Worksheet
1 E3Underwriting Status at E4 is required.
2. State Data General Info ,E4,,Underwriting Status
a. Display error message if this field is blank.
b. Error Message: Yes or No must be selected from the drop down menu in
Worksheet 1 E4.
3. State Data General Info ,, E6, State has interactive tool for small group market
a. Display error message if this field is blank.
b. Error Message: Yes or No must be selected from the drop down menu in
Worksheet 1 E6.Small Group comparison tool at E6 is required
4. State Data General Info ,, E7, State website for small group
a. Display error message if this cell is blank.
b. Error Message: A valid website address must be entered in Worksheet 1Website
Address at E7. is required
5. State Data General Info ,, E9, State has interactive tool for individual market,
a. Display error message if field is blank.
b. Error Message: A value must be selected from the drop down menu in Worksheet
1 E9Individual Market comparison tool at E9 is required.
6. State Data General Info ,, E10, State website for individual market
a. Display error message if this cell is blank.
b. Error Message: A valid website address must be entered in Worksheet 1Website
Address at E10. is required
7. State Data General Info ,, D14, State Data Submission Primary Contact First Name
a. Display error message if cell is blank.
b. Error message: The primary contact name for State Data Submission must be
entered in Worksheet 1Primary Contact name at D14. is required
8. State Data General Info ,, D15, Data Submission Contact Phone Number
a. Display the error message if the cell is blank.
b. Error message: The primary Submission Primary Contact phone number must be
entered in worksheet 1at D15 is required.
9. State Data General Info ,, D16, Data Submission Primary Contact email
a. Display error message if cell is blank.
b. Error Message: A valid primary contact email address must be entered for State
Data Submission primary in worksheet 1Primary Contact email at D16 is required
19
Formatted: English (U.S.)
OMB Control Number: 0938-1086
Formatted: Font: Arial, 10 pt
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Font: Arial
10. State Data General Info ,, D25, Primary Contact nameFirst Name
a. Display error mesasge is cell is blank.
b. Error message: A primary contact Preview Primary Contact name must be entered
in worksheet 1at D25 is required.
11. State Data General Info ,, D26, Primary Contact Phone Number
a. Display error message if cell is blank.
b. Error message: A primary contact Preview Primary Contact phone number must
be entered in worksheet 1at D26 is required.
12. State Data General Info , D27, Primary Contact email
a. Display error message if cell is blank.
b. Error message: A primary contactPreview Primary Contact email must be entered
in Worksheet 1at D27 is required.
13. State Data Issuer & ProductGeneral Info, B5, IssuerF14, Data Submission Primary
Contact Last Name
a. Display error message if cell is blank.
b. Error message: An issuerSubmission Primary Contact last name must be entered
in Worksheet 2 B5at F14 is required.
14. State Data & Product Info, C5, Individual/Small GroupGeneral Info, F25, Data
Validation Primary Contact Last Name
c.a. Display error message if value entered is not one of the options in the drop down
menucell is blank.
d.b.Error message: An option from the drop down menu must be selected in
Worksheet 2 C5Preview Primary Contact last name at F25 is required.
15. State Data Issuer & Product Info, E5, Submission Contact Phone NumberB5, Issuer
Name
e.a. Display error message if cell is blank.
b. Error message: A submission contactIssuer name at B5 is required.
16. State Data & Product Info, C5, Individual/Small Group
c. Display error message if value entered is not one of the options in the drop down
menu.
d. Error message: Individual/Small group at C5 is required.
17. State Data & Product Info, D5, Issuer Data Submission name
e. Display error message if value entered is not one of the options in the drop down
menu.
f. Error message: Issuer Data Submission name at D5 is required
18. State Data Issuer & Product Info, E5, Submission Contact Phone Number
g. Display error message if cell is blank.
f.h. Error message: Issuer Data Submission phone number must be entered in
worksheet 2at E5.
16.19.
State Data & Product Info, F5, Submission Contact email.
g.i. Display error message if cell is blank.
h.j. Error message: A submission contact email must be entered in worksheet 2
F5Issuer Data Submission at F5 is required.
17. State Data & Product Info, G5, Customer Service Contact Phone Number for Consumer
20
Formatted: Font: Arial, 10 pt
Formatted: Numbered + Level: 2 +
Numbering Style: a, b, c, … + Start at: 1 +
Alignment: Left + Aligned at: 0.75" + Tab
after: 1" + Indent at: 1"
Formatted: Numbered + Level: 2 +
Numbering Style: a, b, c, … + Start at: 1 +
Alignment: Left + Aligned at: 0.75" + Tab
after: 1" + Indent at: 1"
OMB Control Number: 0938-1086
Formatted: Font: Arial, 10 pt
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Font: Arial
i. Display error message if cell is blank.
j. Error message: A Customer Service Contact Phone Number contact phone
number must be entered in worksheet 2 G5.
18.20.
State Data & Product Info, H5, Fee-for-service
k. Display error message if cell is blank.
l. Error message: Enter the number of products offered Fee-for the specific product
type. Enter ‘0’ if no product-Service at H5 is offered. Worksheet 2 H5required
19.21.
State Data & Product Info, I5, HMO
m. Display error message if cell is blank.
n. Error message: Enter the number of products offered for the specific product type.
Enter ‘0’ if no product is offered. Worksheet 2 I5
20. State Data & Product Info, J5, PPO
o. Display error message if cell is blank.
p. Error message: Enter the number of products offered for the specific product type.
Enter ‘0’ if no product is offered. Worksheet 2 J5
21. State Data & Product Info, K5, EPO
q.m.
Display error message if cell is blank.
r.n. Error message: Enter the numberNumber of products offered for the specific
product type. Enter ‘0’ if no productHMO at I5 is offered. Worksheet 2
K5required
22. State Data & Product Info, L5, POSJ5, PPO
s.o. Display error message if cell is blank.
p. Error message: Enter the numberNumber of products offered for the specific
product type. Enter ‘0’PPO at J5 is required
23. State Data & Product Info, K5, EPO
q. Display error message if no product is offered. Worksheet 2cell is blank.
r. Error message: Number of EPO at K5 is required
t.24.
State Data & Product Info, L5, POS
s. Display error message if cell is blank.
t. Error message: Number of POS at L5 is required
23.25.
State Data & Product Info, M5, Other/Describe
u. Display error message if cell is blank.
a. Error message: Enter the number of products offered for the specific product type.
Enter ‘0’ if no product is offered. Worksheet 2 M5
a. Error message: Other/describe at M5 is required
21
Formatted: Font: Arial, 10 pt
Formatted: Numbered + Level: 1 +
Numbering Style: 1, 2, 3, … + Start at: 1 +
Alignment: Left + Aligned at: 0.25" + Tab
after: 0.5" + Indent at: 0.5"
OMB Control Number: 0938-1086
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
56
Formatted: Font: Arial, 10 pt
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
Appendix B—Required Fields
State General Info Worksheet:
•
•
•
•
•
•
•
•
•
•
State Name
Underwriting Status
State Small Group Interactive Tool Yes/No
State Small Group Interactive Tool Website Address
State Individual Interactive Tool Yes/No
State Individual Interactive Tool Website Address
Primary Data Submission Contact: First Name, Last Name, Phone Number, Email
Address
Back Up Data Submission Contact: First Name, Last Name, Phone Number, Email
Address
Primary Data Validation Contact: First Name, Last Name, Phone Number, Email Address
Back Up Data Validation Contact: First Name, Last Name, Phone Number, Email
Address
State Issuer & and Product Info: Worksheet:
•
•
•
•
•
Issuer Name,
Individual/Small Group, Submission Market
Issuer Contact: Name, SubmissionPhone Number and Email Address
Issuer Contact Phone
Number, Submission contact Email, Fee- of Products Offered for- Each Product Type
Fee for Service,
HMO, PPO,
PPI
EPO,
POS,
• Other
Formatted: Space Before: Auto, After: Auto
•
State General Info: General Info Box, Primary contact name, phone number, and email.
Validation Contact name, phone number, and email
•
Issuer General Info: Issuer name, Federal EIN, state, Market Coverage, Address line,
City, Zip, Local number, Website address, email, Name, Phone number, Is issuer rated,
rating company, rating type, rating.
•
Product Info: Product name, Product type, Product enrollment, Individual or small group,
Website address, Covers whole state, Phase 2.
•
Geographic Coverage: Cross Reference and Zip code.
22
OMB Control Number: 0938-1086
Formatted: Font: Arial, 10 pt
Formatted: Normal, Don't adjust space
between Latin and Asian text, Don't adjust
space between Asian text and numbers
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
Formatted: Font: Arial
Formatted: Font: Arial, 10 pt
Formatted: Indent: Left: 0.25", Space Before:
Auto, After: Auto
23
| File Type | application/pdf |
| File Title | Health Insurance Oversight System (HIOS) Technical Instructions State |
| Author | CGI Federal |
| File Modified | 2010-05-06 |
| File Created | 2010-05-06 |