Download:
pdf |
pdfOMB Control Number: 0938-1086
Health Insurance Oversight System (HIOS) Excel
Data Submission Tool - States
Technical Instructions
Last updated April 28, 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
1
Introduction ........................................................................................................................2
2
State Data Entry Form Functions .......................................................................................3
2.1
Installation ....................................................................................................................3
2.2
Opening the State Data Entry Form ..............................................................................3
2.3
Circle Invalid .................................................................................................................4
2.4
Finalizing the State Data Entry Form for Submission ....................................................6
2.5
Closing the file ..............................................................................................................7
2.6
Password Protection .....................................................................................................7
3
Troubleshooting and FAQ ..................................................................................................8
3.1
FAQ’s ...........................................................................................................................8
3.2
Support .........................................................................................................................8
4
Appendix A— Critical Errors ...............................................................................................9
4.1
5
State Data Critical Errors and Related Error Messages ................................................9
Appendix B—Required Fields...........................................................................................12
1
OMB Control Number: 0938-1086
Draft 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 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 Services 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
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.1
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.
2.2
2.2.1
Opening the State Data Entry Form
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, 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.
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
3
OMB Control Number: 0938-1086
Draft 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.
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.
2.2.2
Commands
Upon successfully opening a working State Data Entry Form with the macros enabled, specific
buttons will appear in the worksheet 1.
Button names (from left to right):
•
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 State Data Entry Form
workbook as it will remove the cell’s predefined formatting.
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 triggering the Validation process, a red circle will surround cells that do not pass validation
rules. Once the validation rules are corrected the Circle Invalid will disappear.
2.3.1
Printing the file
You must use the standard excel Print function to print the State Data Entry Form.
4
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
2.3.2
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:
5
OMB Control Number: 0938-1086
Draft 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 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.
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
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.
6
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
ο 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.
Example: Final_VA20100427130426_State Data Entry Form.xls
Note: Finalized files will be saved in the same directory where the working file is located.
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.
2.5
Closing the file
When closing the file it is highly recommended that you save all current changes.
2.6
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.
7
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
3
Troubleshooting and FAQ
3.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?
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:
•
•
3.2
, and then click Excel Options.
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.
Support
HIOS Help
If you need additional assistance, please call the HIOS Help Desk at 1-877-343-6507 or email
them at at insuranceoversight@hhs.gov.
8
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
4
Appendix A— Critical Errors
4.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: A State must be selected from the drop down menu in Worksheet
1 E3.
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.
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 1 E7.
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 E9
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 1 E10.
7. State Data General Info ,D14,Primary Contact 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 1 D14.
8. State Data General Info ,D15,Contact Phone Number
a. Display the error message if the cell is blank.
b. Error message: The primary phone number must be entered in worksheet 1 D15.
9. State Data General Info ,D16, 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 1 D16
10. State Data General Info ,D25, Primary Contact name
a. Display error mesasge is cell is blank.
b. Error message: A primary contact name must be entered in worksheet 1 D25.
11. State Data General Info ,D26, Primary Contact Phone Number
a. Display error message if cell is blank.
9
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
b. Error message: A primary contact phone number must be entered in worksheet 1
D26
12. State Data General Info , D27, Primary Contact email
a. Display error message if cell is blank.
b. Error message: A primary contact email must be entered in Worksheet 1 D27.
13. State Data Issuer & Product Info, B5, Issuer Name
a. Display error message if cell is blank.
b. Error message: An issuer name must be entered in Worksheet 2 B5.
14. 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: An option from the drop down menu must be selected in
Worksheet 2 C5.
15. State Data Issuer & Product Info, E5, Submission Contact Phone Number
e. Display error message if cell is blank.
f. Error message: A submission contact phone number must be entered in worksheet
2 E5.
16. State Data & Product Info, F5, Submission Contact email.
g. Display error message if cell is blank.
h. Error message: A submission contact email must be entered in worksheet 2 F5.
17. State Data & Product Info, G5, Customer Service Contact Phone Number for Consumer
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. 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 for the specific product type.
Enter ‘0’ if no product is offered. Worksheet 2 H5
19. 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. Display error message if cell is blank.
r. Error message: Enter the number of products offered for the specific product type.
Enter ‘0’ if no product is offered. Worksheet 2 K5
22. State Data & Product Info, L5, POS
s. Display error message if cell is blank.
t. Error message: Enter the number of products offered for the specific product type.
Enter ‘0’ if no product is offered. Worksheet 2 L5
10
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
23. 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
11
OMB Control Number: 0938-1086
Draft Health Insurance Oversight System – Excel Data Submission
Tool
Technical Instructions
5
Appendix B—Required Fields
•
State Issuer & Product Info: Issuer Name, Individual/Small Group, Submission Contact
Name, Submission Contact Phone Number, Submission contact Email, Fee-for-Service,
HMO, PPO, EPO, POS, Other
•
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.
12
OMB Control Number: 0938-1086
PRA Disclosure Statement
D
ra
f
t
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
Worksheet 1 - State General and Contact Information
1. General Information
1). State Name:
2). Underwriting Status:
3a). Does your State have an interactive health plan comparison tool for
the Small Group market?
3b). Enter the Website address for the Small Group market comparison
tool that includes benefits and prices for discrete plans; or enter the State
website address if "No" is entered above.
4a). Does your State have an interactive health plan comparison tool that
includes benefits and prices for discrete plans for the Individual market?
4b). Enter the Website address for the Individual market; or enter the
State website address if "No" is entered above.
2. State Data Submission Contacts
Primary Contact
Phone Number:
Email:
Backup Contact
Name:
Phone Number:
Email:
ra
ft
Name:
Extension:
Extension:
3. State Data Submission Validation Contacts
Name:
D
Primary Contact
Phone Number:
Email:
Extension:
Backup Contact
Name:
Phone Number:
Email:
Extension:
D
ra
ft
OMB Control Number: 0938-1086
OMB Control Number: 0938-1086
State List
AL
AK
AZ
AR
CA
D
ra
ft
CO
CT
DE
DC
FL
GA
HI
ID
IL
IN
IA
KS
KY
LA
ME
MD
MA
MI
MN
MS
MO
MT
NE
NV
NH
NJ
NM
NY
NC
ND
OH
OK
OR
PA
RI
SC
SD
OMB Control Number: 0938-1086
D
ra
ft
TN
TX
UT
VT
VA
WA
WV
WI
WY
OMB Control Number: 0938-1086
D
ra
ft
Underwriting Status
Yes
No
OMB Control Number: 0938-1086
Worksheet 2 - Issuer and Product Information
Issuer Data Submission Contact
Name
Phone Number
Email
No. of Products offered
Customer Service Contact
Phone Number for
Fee-forConsumers
Service HMO PPO EPO POS Other
D
ra
f
t
Individual/Small
Issuer Name
Group
| File Type | application/pdf |
| File Title | Health Insurance Oversight System (HIOS) Technical Instructions State |
| Author | CGI Federal |
| File Modified | 2010-04-30 |
| File Created | 2010-04-30 |