ACA How-To Part 3: Converting Excel into XML

(This page has been updated for processing year 2017. Key changes are namespaces and the no empty tags stipulation.) (There are some minor changes for processing year 2018 in this page.)

Okay, this is the part that probably actually got you stumped - the actual conversion of Excel into XML. You can probably figure out everything else but if you have tried converting, you probably found out that you can't really convert Excel into XML - at least not directly. When doing the conversion of Excel data, you typically use a schema that contains the fields that you "map" onto your Excel data. You can try to do the same using the government's schema (if you know which one to use) but there is a little problem with parent-child relationships or nesting folders or whatever you want to call it. The bottom line is the government's schema is a little more complicated than what Excel can handle.

But there is a work around, as long as you don't mind the additional step. My method involves stripping down the schema file to the basic required information and then build up the XML using a macro. The real schema file is "IRS-Form 1094-1095CTransmitterUpstreamMessage" but my version is a stripped down version that will enable you to do conversion. The macro will add in the required lines to satisfy the government's schema.

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

A

First of all though, you must make sure your data format is in the right format as the government specifies it: no apostrophes, no strange characters, no double spaces. Use the crosswalk to tell you whether the format is a number or string and how many characters. For instance, addresses are limited to 35 characters (I'm pulling this off the top of my head so you need to check the crosswalk). Down below, I've embedded a template that helps with the formatting and has some business rules check. You should be able to click on the Download button but I'm not 100% certain. It worked on my machine but you never know with these things. You should get a download that contains 2 tabs: a "Data" tab and a "For electronic sub" tab. This file is a modified version that I used and it contains some fake data to get you started. You will have to adjust the number of rows of formulas in the "For electronic sub" tab. Put your data in the "Data" tab and the numbers should flow through to the "For electronic sub" tab with the correct formats. Please check to make sure you do not have strange characters that I might not have thought of or encountered. This file was set up based upon my situation. Off to the far right, you will see some business rules check: basically checking to see that if you have anything in the annual column, you shouldn't have anything in the monthly column and vice versa. You will have to deploy the filters to check on the business rules. I'm not covering all of the business rules; only those that I needed in my situation.

One change for 2017: last year I thought that for the code 2 series (2A, 2B, 2C, etc), you either had to have the annual filed filled or every single month filled. This year I learned that is not true. Normally, you want either one to avoid triggering a red flag, BUT in instances where you have a month(s) that your company did not offer minimum required coverage at minimum required value to at least 95% of your full time employees, then that month(s) should not have a safe harbor code (2H, 2I, maybe 2G, I don't remember exactly which ones). So the business rules in the file will have to be considered in light of this new information.

This file is to just get you started. It will not cover all of your situations and I don't claim to cover everything.

What this file does is to lay down the basic data you will need to convert. By the way, I need to repeat, this process does not cover self-insurance. You might have to adjust the file to cover for that situation. Once you have your data in the "Data" tab and you have checked the formats and business rules, you are ready for the next step.

Note for 2018 changes: none in this file. The basic layout remains the same.

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

B

The middle section below contains the stripped xml schema that you will place into your xml file, using the Notepad. I actually cannot upload this xml file so you will have to copy the code from the middle column into a Notepad and then save it as an XML file. This will be your "schema" file that will instruct Excel on how to convert your Excel data. But first, you need to replace Record 1 and Record 2 in the code with your Record 1 and Record 2. Don't use the fake data in the code below. The other change you may need to do is if your employee has the same minimum monthly cost for every month and thus you use the annual field or tag, then you will need to add that tag to the schema and to the macro. Our insurance premiums increased in the middle of the year so I could not use the annual field.

Be sure to save this file as an XML file.

Note for 2017: the stripped down file I created has been changed for 2017 processing year because of the requirement for no empty tags. This stipulation necessitated some changes in the macro which impacted the stripped schema file.

Note for 2018: the stripped down file will contain a minor change - tag <MonthlyShareOfLowestCostMonthlyPremGrp> has been changed to <MonthlyEmployeeRequiredContriGrp>.

Once you've adjusted the code to use your Record 1 and 2, you are ready to start the conversion process. This is done via the Developer tab in the Ribbon. If you don't have the Developer tab, you will need to do something like File-Option-Customize Ribbon, depending on which Excel version you have. Once you have the Developer tab, you should see an XML section and a tool called Source. Click on that Source and an XML Source Box should pop up on the right hand side. At the bottom, you should see an XML Maps button, click on it and follow along to find your schema xml file (the one with your Record 1 and Record 2). "Add" your schema file. You will get a message something like "The specified XML source does not refer to a schema. Excel will create a schema based on XML Source data." That's okay. Just click okay.

Now you will begin to "map" or drag the fields sitting in the XML Source Box over to the field in the template with your data. The field names should be very similar, if not exactly the same. The only ones you won't map over are the folders and the very first one. Otherwise, the rest should be mapped over. For the first one that you try to map or drag, you will receive a message similar to "Data you are attempting to map contains formatting…." You will get two choices on what to do: pick the first one relating to "Use existing formatting".

Once you've mapped over everything, you are ready to verify that the mapped data is exportable. There is a link or button in the lower portion of the XML Source Box that says "Verify map for export". If it says that the data is exportable, then you can hit the "export" tool sitting in the Developer tab, close to the "Source" tool. If it doesn't give you a positive, you might have to start over again to see if maybe you hadn't hit something wrong.

After hitting the export tool and saving the result, you now have a basic XML file for your 1095C data form. But you are not done yet. The next step is to run a macro that removes the resulting empty tags and supplies the remainder of the codes necessary to pass the government's schema.

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

C

Note for processing year 2017: IRS has stipulated that the xml file contains no empty tags, so this macro has changed dramatically to incorporate this new rule. Also namespaces have changed so the prefixes are different.

Note for processing year 2018: Due to the change in one of the tags mentioned in prior section, the "Replacement List" tab changed slightly. The macro itself did not.

The section next to the middle XML code contains a file with a macro. If the download is successful, you will have a file with 2 tabs and a FindAndReplace macro. In case the macro piece does not go along with the download, the tab "Macro" contains the macro code to copy and paste into the VBA editor. To get to the VBA Editor, you go to the Developer tab, click on the "Visual Basic" tool and that should lead you directly to the editor. You can then paste the macro copy into the main section on the right.

How do you know if the macro come through the download? Usually when a button with "Enable Content" or something similar shows up in a yellow bar running beneath the menu. That yellow bar is a type of security message. If you get that you most likely have the macro. The macro is called "FindAndReplace". The first thing you want to do, before running the macro, is to designate where your XML is located. You will have to get into the macro to change the location. Go to Developer tab, click on Macro tool and choose FindAndReplace. Pick Edit to actually get into the macro. Now, in the middle, where you see:

FileName = "C:...Users...vfrizzell...Documents...Veronique...Notepad macro testing...ACA 1095C 1.xml"

(Replace ... with a back slash. WordPress keeps eliminating the back slash).

Change that directory path.

Once you've done that, you are ready to click on that macro again in the Macro tool and click on Run. The macro runs blazingly fast. It will tell you when it's done because it is so fast you might think it was still going. Check the XML file before you run the macro and then look at it again after you run the macro. You will see the changes in the XML file. You will find that some field elements have been changed to include "irs:", you will see additional lines added, and some fields deleted due to being empty. These changes come from the tab "Replacement List". This tab tells the macro what to search for and how to replace the searched items.

One thing I should point out: my dollar amounts include cents. If your lowest cost premium is a straight dollar and no cents such as $105 rather than $105.32, the macro may have to be modified to change the $105 to $105.00. You need the decimal places and I believe the Excel xml conversion does away with the decimals if you have zeroes for cents. You will have to modify the macro to search for 105 and replace with 105.00. That's the only thing I can think of that you might have to adjust because my situation may be different.

Also, if you use the tag for the annual employee's share of the monthly minimum cost (that sounds weird), then you will need to add it somehow to the macro, probably via the "Replacement List" tab somewhere. And don't forget to add it in the schema file in Part B if you haven't done it yet.

After running that macro, you now have the complete 1095C forms. The next thing you need to do is append the 1094C form.

[ap_tab_group type="horizontal"]
[ap_tab title="2017 A"]

Download [1.47 MB]

[/ap_tab]
[ap_tab title="2017 B"]

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Form109495CTransmittalUpstream xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Form1095CUpstreamDetail lineNum="0" recordType="String">
<RecordId>1</RecordId>
<CorrectedInd>0</CorrectedInd>
<TaxYr>2016</TaxYr>
<PersonFirstNm>FirstName1</PersonFirstNm>
<PersonLastNm>LastName1</PersonLastNm>
<PersonNameControlTxt>LAST</PersonNameControlTxt>
<TINRequestTypeCd>INDIVIDUAL_TIN</TINRequestTypeCd>
<SSN>102345678</SSN>
<AddressLine1Txt>11111 Address1</AddressLine1Txt>
<AddressLine2Txt></AddressLine2Txt>
<CityNm>City1</CityNm>
<USStateCd>ST1</USStateCd>
<USZIPCd>10234</USZIPCd>
<ALEContactPhoneNum>1213456789</ALEContactPhoneNum>
<StartMonthNumberCd>01</StartMonthNumberCd>
<EmployeeOfferAndCoverageGrp>
<AnnualOfferOfCoverageCd>1H</AnnualOfferOfCoverageCd>
<JanOfferCd></JanOfferCd>
<FebOfferCd></FebOfferCd>
<MarOfferCd></MarOfferCd>
<AprOfferCd></AprOfferCd>
<MayOfferCd></MayOfferCd>
<JunOfferCd></JunOfferCd>
<JulOfferCd></JulOfferCd>
<AugOfferCd></AugOfferCd>
<SepOfferCd></SepOfferCd>
<OctOfferCd></OctOfferCd>
<NovOfferCd></NovOfferCd>
<DecOfferCd></DecOfferCd>

<Form1095CUpstreamDetail lineNum="0" recordType="String">
<RecordId>2</RecordId>
<CorrectedInd>0</CorrectedInd>
<TaxYr>2016</TaxYr>
<PersonFirstNm>FirstName2</PersonFirstNm>
<PersonLastNm>LastName2</PersonLastNm>
<PersonNameControlTxt>LAST</PersonNameControlTxt>
<TINRequestTypeCd>INDIVIDUAL_TIN</TINRequestTypeCd>
<SSN>123456789</SSN>
<AddressLine1Txt>Address2</AddressLine1Txt>
<AddressLine2Txt></AddressLine2Txt>
<CityNm>City2</CityNm>
<USStateCd>ST</USStateCd>
<USZIPCd>11111</USZIPCd>
<ALEContactPhoneNum>1111111111</ALEContactPhoneNum>
<StartMonthNumberCd>01</StartMonthNumberCd>