Excel Macro
Budget: $30 – $250 USD
General Requirements
- The Macro will get a CSV format and will create an excel based on the data provided in the first one.
- The Macro will run on Windows
- Will be able for maintenance and well written
- Support of German character set
- The developer will describe how tests were performed.
Detailed requirements
Use The exported csv file
The output should be an excel file
Remove the time from date fields (if exists)
In The GL account code, leave only the first one, and delete the rest
Any Decimal number should be cut to up to 2 digits following the point.
We will duplicate each transaction line per the number of attendees.
Each line will have only up to one attendee (not multiple as today).
Each attendee's Taxable amount will be calculated as the Total taxable amount/ No of Attendees.
Each attendee's Approved amount will be calculated as the Total approved amount/ No of Attendees.
Each attendee's decision will be the same as the transaction decision,
Note that the number of columns in the input file will change based on the number of attendees.
Note that in case unique chars are used, they should be presented properly as part of the excel e.g. Lüdenscheid
Note that the file might include extra columns, later on, in such a case all the added columns should be duplicated to the attendee specific line (same logic as the existing fields)
Move “Paid Date” to be before “Transaction Date”
Remove “Taxable Decision Date”
Update external ID title to “Attendee ID"
Update the Attendee fields titles to be “Attendee Type”, “Company”, “Title”
Add new empty columns after “Attendee ID”, titled “Payroll ID“, ”Normal Place of Work”, “Job Type”
in case the following columns don’t exist add them at the end:
Sub-Decision
Reporting
Wage Type
Wage Type client description (for payslip)
Payer Wage Tax - employee spend
Payer Wage Tax - 3rd party spend
Payer Social Securities - employee spend
Payer Social Securities - 3rd party spend
Attached:
1. Example input
2 Example output for the example input
- The Macro will get a CSV format and will create an excel based on the data provided in the first one.
- The Macro will run on Windows
- Will be able for maintenance and well written
- Support of German character set
- The developer will describe how tests were performed.
Detailed requirements
Use The exported csv file
The output should be an excel file
Remove the time from date fields (if exists)
In The GL account code, leave only the first one, and delete the rest
Any Decimal number should be cut to up to 2 digits following the point.
We will duplicate each transaction line per the number of attendees.
Each line will have only up to one attendee (not multiple as today).
Each attendee's Taxable amount will be calculated as the Total taxable amount/ No of Attendees.
Each attendee's Approved amount will be calculated as the Total approved amount/ No of Attendees.
Each attendee's decision will be the same as the transaction decision,
Note that the number of columns in the input file will change based on the number of attendees.
Note that in case unique chars are used, they should be presented properly as part of the excel e.g. Lüdenscheid
Note that the file might include extra columns, later on, in such a case all the added columns should be duplicated to the attendee specific line (same logic as the existing fields)
Move “Paid Date” to be before “Transaction Date”
Remove “Taxable Decision Date”
Update external ID title to “Attendee ID"
Update the Attendee fields titles to be “Attendee Type”, “Company”, “Title”
Add new empty columns after “Attendee ID”, titled “Payroll ID“, ”Normal Place of Work”, “Job Type”
in case the following columns don’t exist add them at the end:
Sub-Decision
Reporting
Wage Type
Wage Type client description (for payslip)
Payer Wage Tax - employee spend
Payer Wage Tax - 3rd party spend
Payer Social Securities - employee spend
Payer Social Securities - 3rd party spend
Attached:
1. Example input
2 Example output for the example input