CSV data manipulated through XLS to create another CSV using references and lookups
- or -
Post a project like this2051
£100(approx. $125)
- Posted:
- Proposals: 4
- Remote
- #2121221
- Awarded
Description
Experience Level: Expert
Estimated project duration: less than 1 week
Firstly, thank you for taking the time to review this. We are looking at a way of taking all the visits (visits.csv) taken from 1 system called Time To Pet (TTP) and, using reference tables, manipulate the data and create an output CSV that we can then import into our accounting package called KashFlow (KF).
We need a flexible solution that allows us to add new users and services and rates to the final solution as well as being able to insert monthly visits and customer codes etc for processing easily.
Thx. Ricky
Here we go…
So, if you have read through the above you will get an understanding of the process so far.
We have a file called visits.csv which lists all the care visits for our pet owners for a full month. You can see a full month’s example attached to this job post. Those without a duration have not yet been completed but do not worry about this as it is not relevant.
Our aim to be able to summarise the value of the jobs that have been undertaken and work out how much money in total is to be paid to the business (The Pet Manny) and how much is to be distributed to our freelancers/subcontractors based on reference tables.
We would like to have this all manipulated in one XLS where there are additional sheets for reference/lookups. If you can think of a better way to operate then feel free to recommend to us.
There is a brief example of the desire output required attached to this job named Final Output ready for Import.csv which has manually been created from a smaller visits.csv (attached called small example visits.csv)
FIELD BY FIELD BREAKDOWN and CALCULATIONS REQUIRED for FINAL OUTPUT READY FOR IMPORT
IMPORTANT NOTE – ANY LINES FOR CLIENT THE PET MANNY MUST BE DISREGARDED. ALSO ANY LINES THAT STATE A SERVICE AS RISK/REWARD ADMIN FINDERS’ FEE MUST ALSO BE INGNORED
IT WOULD ALSO BE GREAT IF THERE WAS A WAY TO FLAG UP ANY RECORD THAT CANNOT BE PROCESSED OR IGNORED SO THAT IT CAN BE MANUALLY FIXED BEFORE PROCESSING
1. Customer Code – taken from a look up on full name from customers from KF.xlsx.
2. Invoice Number – automatically generated, unique and sequential. Starting from a fixed number above 5000 set somewhere is the xls. Each invoice may have more than 1 line so in that case the invoice number would be the same.
3. Invoice Date – set as the last date for the month of jobs. E.g. If all the visits are August 18 then the invoice date should be set as 31/08/2018
4. Due Date – this date should be 28 days after the invoice date
5. Customer Reference – this is the pets names taken from client field in visits. For example if the client field reads Algie, Rosie, Sam, Dean (Christine Powell ) then is the customer reference Algie, Rosie, Sam, Dean
6. Line-Quantity – This is always set to 1 as we are grouping all differing visits for each client together.
7. Line-Description. So, this is where is get tricky and there are a lot of calculations and look-ups to do. On each invoice there will be 2 lines. The first line is the total of money for THE PET MANNY Ltd and the second if the amount of money due to our freelancers.
a. FIRST LINE (date taken from visits.csv) should read…
i. Aug 2018
Care directly from The Pet Manny Ltd employees plus subcontractor commission
ii. Aug 2018
Subcontractors fee
8. Line-Charge Type. 2 separate values for this field based on the field above
i. Care directly from The Pet Manny Ltd line must state - Sale of Services
ii. Subcontractors fee line must state - Creditors Control Account
9. Line Rate. This is where the calculations come in to play. The USER will denote how we split the financial value between the 2 lines. This can be done by a look up with the USER sheet added to this example as users and rates.xlsx. Each user can have a different rate for each service.
a. The first line (Care directly from The Pet Manny Ltd) should be a total of the following…
i. The full value of any services listed against a user listed as Employee in the users and rates.xlsx (the total of Service Revenue, Pet Fees and Holiday Fees in visits.csv PLUS…
ii. The commission for TPM based on the percentage rate listed against the service and the user in users and rates.xlsx (This amount calculated from the total of Service Revenue, Pet Fees and Holiday Fees in visits.csv). For example, if Tyler Potts has a dog walk for £10 and Pet Fees of £10 and Holiday Fees of £10 then the commission for this at 75% (£7.50) should be added to this line total.
b. The second line (Subcontractors fee) is the remaining of the value not included in the previous line. For example. If Tyler Potts has a dog walk for £10 then the subcontractors fee is £7.50 for this line. The £2.50 remaining would have been added to the line above.
10. Line-VAT Amount = 0 (always)
11. Line VAT Rate = 0 (always)
12. N/A VAT = -1 (always)
FOR SOME STUPID REASON I CAN ONLY UPLOAD 3 FILES TO THIS JOB - I HAVE MERGED ALL THE XLS'S INTO ONE
We need a flexible solution that allows us to add new users and services and rates to the final solution as well as being able to insert monthly visits and customer codes etc for processing easily.
Thx. Ricky
Here we go…
So, if you have read through the above you will get an understanding of the process so far.
We have a file called visits.csv which lists all the care visits for our pet owners for a full month. You can see a full month’s example attached to this job post. Those without a duration have not yet been completed but do not worry about this as it is not relevant.
Our aim to be able to summarise the value of the jobs that have been undertaken and work out how much money in total is to be paid to the business (The Pet Manny) and how much is to be distributed to our freelancers/subcontractors based on reference tables.
We would like to have this all manipulated in one XLS where there are additional sheets for reference/lookups. If you can think of a better way to operate then feel free to recommend to us.
There is a brief example of the desire output required attached to this job named Final Output ready for Import.csv which has manually been created from a smaller visits.csv (attached called small example visits.csv)
FIELD BY FIELD BREAKDOWN and CALCULATIONS REQUIRED for FINAL OUTPUT READY FOR IMPORT
IMPORTANT NOTE – ANY LINES FOR CLIENT THE PET MANNY MUST BE DISREGARDED. ALSO ANY LINES THAT STATE A SERVICE AS RISK/REWARD ADMIN FINDERS’ FEE MUST ALSO BE INGNORED
IT WOULD ALSO BE GREAT IF THERE WAS A WAY TO FLAG UP ANY RECORD THAT CANNOT BE PROCESSED OR IGNORED SO THAT IT CAN BE MANUALLY FIXED BEFORE PROCESSING
1. Customer Code – taken from a look up on full name from customers from KF.xlsx.
2. Invoice Number – automatically generated, unique and sequential. Starting from a fixed number above 5000 set somewhere is the xls. Each invoice may have more than 1 line so in that case the invoice number would be the same.
3. Invoice Date – set as the last date for the month of jobs. E.g. If all the visits are August 18 then the invoice date should be set as 31/08/2018
4. Due Date – this date should be 28 days after the invoice date
5. Customer Reference – this is the pets names taken from client field in visits. For example if the client field reads Algie, Rosie, Sam, Dean (Christine Powell ) then is the customer reference Algie, Rosie, Sam, Dean
6. Line-Quantity – This is always set to 1 as we are grouping all differing visits for each client together.
7. Line-Description. So, this is where is get tricky and there are a lot of calculations and look-ups to do. On each invoice there will be 2 lines. The first line is the total of money for THE PET MANNY Ltd and the second if the amount of money due to our freelancers.
a. FIRST LINE (date taken from visits.csv) should read…
i. Aug 2018
Care directly from The Pet Manny Ltd employees plus subcontractor commission
ii. Aug 2018
Subcontractors fee
8. Line-Charge Type. 2 separate values for this field based on the field above
i. Care directly from The Pet Manny Ltd line must state - Sale of Services
ii. Subcontractors fee line must state - Creditors Control Account
9. Line Rate. This is where the calculations come in to play. The USER will denote how we split the financial value between the 2 lines. This can be done by a look up with the USER sheet added to this example as users and rates.xlsx. Each user can have a different rate for each service.
a. The first line (Care directly from The Pet Manny Ltd) should be a total of the following…
i. The full value of any services listed against a user listed as Employee in the users and rates.xlsx (the total of Service Revenue, Pet Fees and Holiday Fees in visits.csv PLUS…
ii. The commission for TPM based on the percentage rate listed against the service and the user in users and rates.xlsx (This amount calculated from the total of Service Revenue, Pet Fees and Holiday Fees in visits.csv). For example, if Tyler Potts has a dog walk for £10 and Pet Fees of £10 and Holiday Fees of £10 then the commission for this at 75% (£7.50) should be added to this line total.
b. The second line (Subcontractors fee) is the remaining of the value not included in the previous line. For example. If Tyler Potts has a dog walk for £10 then the subcontractors fee is £7.50 for this line. The £2.50 remaining would have been added to the line above.
10. Line-VAT Amount = 0 (always)
11. Line VAT Rate = 0 (always)
12. N/A VAT = -1 (always)
FOR SOME STUPID REASON I CAN ONLY UPLOAD 3 FILES TO THIS JOB - I HAVE MERGED ALL THE XLS'S INTO ONE
Ricky D.
100% (1)Projects Completed
1
Freelancers worked with
1
Projects awarded
100%
Last project
7 Sep 2018
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies