Standard 401k to Betterment
Oct 29, 2024
This article will describe the file transformation that has been created in detamoov to transform the standard 401k file format to the Betterment file specifications. This transformation is used in a detamoov integration with Betterment.
| Source File | Destination file |
| Standard 401k | Betterment |
File Specifications
| File Type Generated | CSV |
| Header Row(s) Generated | Yes |
| Footer Row(s) Generated |
Yes |
| End of Row Character | CRLF |
| File Naming Generated |
[Plan ID]_[Unique Id]_[Check Date in yyyymmdd].csv |
Field Mappings
The tables below detail the specific field mappings and transformation rules applied to ensure the Betterment csv format is created correctly from the standard 401k format.
Header Row
| Betterment Field Name | Standard 401k Field Name | Field Type | Details |
|
Record Type ID |
|
STRING |
|
|
Plan ID |
PLAN ID |
STRING |
|
|
File Format Version Number |
|
|
Hardcoded 01 |
|
External Payroll ID |
Unique value per file |
|
GUID |
|
Pay Period Begin Date |
PAY PERIOD START DATE |
DATE |
YYYYMMDD |
|
Pay Period Ending Date |
PAY PERIOD END DATE |
DATE |
YYYYMMDD |
|
Payroll Date |
PAYROLL CHECK DATE |
DATE |
YYYYMMDD |
|
Effective Date |
PAYROLL CHECK DATE |
DATE |
YYYYMMDD |
|
Data Retrieved At |
|
|
Unix timestamp |
Body
| Betterment Field Name | Standard 401k Field Name | Field Type | Details |
| Record Type ID | STRING | Hardcoded 'PAY' | |
| Plan ID | PLAN ID | STRING | |
| Social Security Number | SSN | NUMBER | Numeric only |
| Last Name | LAST NAME | STRING | |
| First Name | FIRST NAME | STRING | |
| Middle Initial | STRING | Middle initial only. | |
| Employee Traditional Contribution | CURRENT PAYROLL EMPLOYEE PRETAX CONTRIBUTION | NUMBER | |
| Employee Roth Contribution | CURRENT PAYROLL EMPLOYEE ROTH CONTRIBUTION | NUMBER | |
| Employer Match | CURRENT PAYROLL EMPLOYER MATCH CONTRIBUTION | NUMBER | |
| Profit Sharing | CURRENT PAYROLL NON ELECTIVE CONTRIBUTION | NUMBER | |
| Safe Harbor Match | CURRENT PAYROLL SAFE HARBOR EMPLOYER MATCH | NUMBER | |
| Safe Harbor Nonelective | CURRENT PAYROLL SAFE HARBOR NON ELECTIVE | NUMBER | |
| QACA Safe Harbor Match | NUMBER | ||
| QACA Safe Harbor Nonelective | NUMBER | ||
| Loan 1 ID | 401k Loan 1 Id | STRING | |
| Loan 1 Payment | 401k Loan 1 Repayment | NUMBER | |
| Loan 2 ID | 401k Loan 2 ID | STRING | |
| Loan 2 Payment | 401k Loan 2 Repayment | NUMBER | |
| Loan 3 ID | STRING | ||
| Loan 3 Payment | NUMBER | ||
| Loan 4 ID | STRING | ||
| Loan 4 Payment | NUMBER | ||
| Loan 5 ID | STRING | ||
| Loan 5 Payment | NUMBER | ||
| Pay Period Eligible Compensation | CURRENT PAY PERIOD GROSS PAY | NUMBER | |
| Pay Period Excluded Compensation | NUMBER | ||
| Pay Period Total Compensation | CURRENT PAY PERIOD GROSS PAY | NUMBER | |
| Pay Period Hours Worked | CURRENT PAY PERIOD HOURS WORKED | NUMBER | Required if plan sponsor has elected to use hours of service for vesting, eligibility, or allocation purposes.Otherwise, optional. |
| Plan YTD Employee Traditional Contribution | NUMBER | ||
| Plan YTD Employee Roth Contribution | NUMBER | ||
| Plan YTD Employer Match | NUMBER | ||
| Plan YTD Profit Sharing | NUMBER | ||
| Plan YTD Safe Harbor Match | NUMBER | ||
| Plan YTD Safe Harbor Nonelective | NUMBER | ||
| Plan YTD QACA Safe Harbor Match | NUMBER | ||
| Plan YTD QACA Safe Harbor Nonelective | NUMBER | ||
| Plan YTD Eligible Compensation | NUMBER | ||
| Plan YTD Excluded Compensation | NUMBER | ||
| Plan YTD Total Compensation | YTD GROSS PAY | NUMBER | |
| Plan YTD Hours Worked | YTD HOURS WORKED | NUMBER | |
| Estimated Annual Compensation | NUMBER | Optional, but submit if hours of service are used for vesting, eligibility, or allocation. | |
| Pay Frequency | PAY PERIOD START DATE, PAY PERIOD END DATE | STRING | |
| Birth Date | DOB | NUMBER | YYYYMMDD |
| Hire Date (original) | HIRE DATE | NUMBER | YYYYMMDD |
| Termination Date | TERMINATION DATE | NUMBER | YYYYMMDD Required only if Employment Status = "T" but not if the EE is transferring from one division to another |
| Termination Reason | STRING | Voluntary = V Involuntary = I Death = D Total Disability = T |
|
| Rehire Date (date of most recent rehire) | REHIRE DATE | NUMBER | YYYYMMDD |
| Leave of Absence Type | STRING | Military = M FMLA = F Paid = P Unpaid = U |
|
| Leave of Absence Begin Date | NUMBER | ||
| Leave of Absence End Date | NUMBER | ||
| Employment Status | Employment Status | STRING | Active = A Terminated = T Deceased =C Disabled = D On Leave = L Retired = R |
| Officer Status Code | STRING | Officer = O Non-officer = N |
|
| Ownership Percentage | NUMBER | 10.00 = 10% | |
| Union Member | STRING | ||
| Date Union Status Changed | NUMBER | ||
| Full Time Indicator | STRING | ||
| Date Full Time Status Changed | NUMBER | ||
| Part Time Indicator | STRING | ||
| Date Part Time Status Changed | NUMBER | blank if not applicable | |
| Intern Indicator | STRING | blank if not applicable | |
| Date Intern Status Changed | NUMBER | ||
| Temporary Indicator | STRING | blank if not applicable | |
| Date Temporary Status Changed | NUMBER | blank if not applicable | |
| Seasonal Indicator | STRING | blank if not applicable | |
| Date Seasonal Status Changed | NUMBER | ||
| Contractor Indicator | STRING | ||
| Date Contractor Status Changed | NUMBER | blank if not applicable | |
| Leased Employee | STRING | blank if not applicable | |
| Date Leased Employee Status Changed | NUMBER | ||
| Non-Resident Alien | STRING | ||
| Date Non-Resident Alien Status Changed | NUMBER | blank if not applicable | |
| Other Excluded Class | STRING | blank if not applicable | |
| Date Other Excluded Class Status Changed | NUMBER | blank if not applicable | |
| External Employee ID | EMPLOYEE NUMBER | STRING | |
| Subsidiary EIN | NUMBER | ||
| Reporting Division | STRING | ||
| Residential Address Line #1 | ADDRESS LINE 1 | STRING | Quote Field |
| Residential Address Line #2 | ADDRESS LINE 2 | STRING | Quote Field |
| Residential City | CITY | STRING | |
| Residential State | STATE | STRING | |
| Residential Zip Code | ZIP CODE | STRING | First 5 digits |
| Residential Country | STRING | ||
| Email Address | STRING | ||
| Electronic Access | STRING | Electronic access = E Non-electronic access = N |
|
| Marital Status | MARITAL STATUS | STRING | Single = S Married = M |
Footer
| Betterment Field Name | Standard 401k Field Name | Field Type | Details |
| Record Type ID | STRING | Hardcode 'FTR' | |
| Plan ID | PLAN ID | STRING | |
| Pay Period Ending Date | PAY PERIOD END DATE | NUMBER | YYYYMMDD |
| Number of Records | NUMBER | Total # of records sent | |
| ACH Debit Amount | NUMBER | Sum of all contributions and loan repayments across all employees | |
| Employee Traditional Contribution | NUMBER | Total PreTax Amount | |
| Employee Roth Contribution | NUMBER | Total Roth Amount | |
| Employer Match | NUMBER | Total Employer Match | |
| Profit Sharing | NUMBER | Total Nonelective Contribution | |
| Safe Harbor Match | NUMBER | Total Safe Harbor Match | |
| Safe Harbor Nonelective | NUMBER | Total Safe Harbor Non Elective | |
| QACA Safe Harbor Match | NUMBER | No default mapping | |
| QACA Safe Harbor Nonelective | NUMBER | No default mapping | |
| Loan Payment Total | NUMBER | Total Loan 1 and 2 Repayment | |
| Total Pay Period Eligible Compensation | NUMBER | Total Current Pay Period Gross Pay | |
| Total Pay Period Excluded Compensation | NUMBER | No default mapping | |
| Total Pay Period Total Compensation | NUMBER | Total Current Pay Period Gross Pay | |
| Total Pay Period Hours Worked | NUMBER | Total Current Pay Period Hours Worked |
