Upload CSV for paying individuals out

[balanced]

Upload CSV for paying individuals out

[mahmoudimus]

@NihalPartha has started giving CSV field names here.

Love this idea.

For a little more detail, here's what we'd like to include in the file:

• Account Name
• Account Number
• Routing Number
• Checking or Savings
• Memo
• Amount

More ideal would be to save all the banking details (first 5 bullets) within Balanced, so that these values wouldn't be stored in the CSV. Then, we'd just need to include Account Name, Memo, Amount - Balanced would know who to route the money to.

Last thing - if there isn't enough money in our account to pay the full amount generated by the CSV, the whole thing should fail, so we can decide who to pay (rather than letting the system decide).

[arun057]

it could also be a hash (when calling from the api)

[timnguyen]

If this feature is built, Balanced should be doing some rudimentary automated error checking:

1. routing number lookup
2. bank account checksum
3. Dupe detection within same day/batch, potentially across batches

Basically run on the CSV highlight possible problems and let people confirm before actually processing.

[dylandrop]

Hi, Jareau Wade asked me to chime in here about my opinion on paying out to multiple sellers at once, so I guess I'll just summarize what we talked about / my opinions. I think it would be great if you could send a bulk request sort of like this:

{ charges:
[
{seller_id: 12, amount: 400}, 
{seller_id: 13, amount: 400},
{seller_id: 14, amount: 400},
{seller_id: 15, amount: 400}
]
card_number: 1283123412341234
...
}

etc. I'm not sure how the error handling would work, but this is a feature that really needs to be implemented. PayPal has this ability with Parallel/Chained Payments (https://developer.paypal.com/webapps/developer/docs/classic/adaptive-payments/integration-guide/APIntro/) and it would really make a difference if Balanced had this as well.

[mjallday]

@dylandrop how does the format of the current CSV download in the dashboard work for you? it is a superset of your requested data I believe.

[edit]

oops sorry, i mis-read this.

[zachgersh]

Is there actually an API endpoint available to support this feature request (as of yet)? I am assuming something must be around since this was opened six months ago :D

[dylandrop]

No idea sorry.. I don't use the Balanced API, so a Balanced engineer would probably know better than I.

[kleinsch]

There's no API endpoint that takes a CSV file and does payouts. We do payouts in the dashboard via the API right now (see the pay a seller modal), so you could conceivably use the FileReader API to load the CSV file, parse it, then fire off one API request per line to do the payouts.

[zachgersh]

I love this idea. Would there be a maximum to the amount of lines you would want in this CSV?

On Oct 2, 2013, at 7:48 AM, Nick Kleinschmidt [email protected] wrote:

There's no API endpoint that takes a CSV file and does payouts. We do payouts in the dashboard via the API right now (see the pay a seller modal), so you could conceivably use the FileReader API to load the CSV file, parse it, then fire off one API request per line to do the payouts.

—
Reply to this email directly or view it on GitHub.

[kleinsch]

Let me talk to a couple people today and see if we can get a spec and visual design for this banged out. I'll update later in the day with details.

[NihalPartha]

Would LOVE this - we don't use the payouts API so we have to enter the info
one by one for each payment we send.
A CSV would be a lifesaver.

On Wed, Oct 2, 2013 at 11:39 AM, Nick Kleinschmidt <[email protected]

wrote:

Let me talk to a couple people today and see if we can get a spec and
visual design for this banged out. I'll update later in the day with
details.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/21#issuecomment-25549382
.

Nihal Parthasarathi
203 645 5151
Co-Founder, CourseHorsehttp://coursehorse.com/?utm_source=Email%2BSignature&utm_medium=Email&utm_content=Email%2BSignature&utm_campaign=Email%2BSignature
Linked In http://www.linkedin.com/in/nihalparthasarathi |
Twitterhttp://twitter.com/#%21/nihalpartha
| Blog http://www.ifnotnowthennever.com/

As Featured in:
WSJhttp://blogs.wsj.com/metropolis/2011/07/22/culture-city-online-a-metropolis-of-learning-all-in-one-place/
| BIhttp://www.businessinsider.com/coursehorse-nyu-stern-startup-winners-katie-kapler-nihal-parthasarathi-2011-7
| Crain's NYhttp://www.crainsnewyork.com/article/20110821/SMALLBIZ/308219987
| TheNextWebhttp://thenextweb.com/apps/2011/07/21/in-nyc-coursehorse-is-changing-the-way-you-search-for-classes/
| Mashable http://mashable.com/2011/07/02/3-new-apps/

[zachgersh]

Seems like @kleinsch is going to help get the ball rolling on this and then I will pick it up and run with it @NihalPartha :D

[kleinsch]

@zachgersh Our designer is going to have to think through how the user interface for this would work. He'll upload specs when he's ready. The tricky part is figuring out how to handle errors, especially if some payments succeeded and some failed. It might take a few days for him to get his thoughts together, I'll let you know when we're ready to go.

[zachgersh]

Sounds good! I will keep an eye out for when this rolls in. Very
interested in working on this project :)

On Wed, Oct 2, 2013 at 3:41 PM, Nick Kleinschmidt
[email protected]:

@zachgersh https://github.com/zachgersh Our designer is going to have
to think through how the user interface for this would work. He'll upload
specs when he's ready. The tricky part is figuring out how to handle
errors, especially if some payments succeeded and some failed. It might
take a few days for him to get his thoughts together, I'll let you know
when we're ready to go.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/21#issuecomment-25583355
.

[dmdj03]

Structure

USE CASE 1: Paying sellers with a customer account
USE CASE 2: Paying sellers without a customer account (customer is created in the process) - Do we support use case 2?

Required components:
• Interface to upload CSV
• CSV file formatted

CSV FILE FORMATTING:
The contents of each file is a table where each row is a unique payout to a unique customer and each column is a different piece of customer-related information.

Limit: Max number of payouts in one batch?

USE CASE 1:
• Customer ID (required) - Customer ID is the surest way to identify the correct customer in the event that an email address is associated with multiple customers.
• Bank Account ID (required) - For customers with more than one bank account
• Amount (required)
• Appears_on_statement_as (optional)
• Internal description (optional)

USE CASE 2:
• Customer name (required)
• Customer email (required)
• Bank Account account number (required)
• Bank Account routing number (required)
• Account holder’s name (required)
• Account type (required)
• Amount (required)
• Appears_on_statement_as (optional)
• Internal description (optional)

USE CASE 1 data and USE CASE 2 data cannot be combined in one file.

INTERFACE TO UPLOAD CSV:
• Instructions explaining accepted formats
• Download template for use case 1 or use case 2 (user may also supply their own file, formatted correctly)
• Select file
• Map fields (map csv fields to Balanced accepted fields)
• Review data (summary of good and erroneous data and total amount to be paid out)
• Error types
-----General:
----------Invalid file format
----------Insufficient funds in escrow
-----Use Case 1:
----------Missing field
----------Unable to locate customer ID
----------Unable to locate bank account ID
----------Invalid amount formatting
-----Use Case 2:
----------Missing field
----------Invalid amount formatting
----------Invalid email formatting
----------Invalid routing number
• Option to edit and correct the data through the interface? or re-uploading a corrected file?
• Submit data
• Confirmation

REVIEW A BATCH SUBMISSION AT A LATER DATE:
Is there a way to review the credit statuses of all payouts in a single batch? Should there be a credit batch filter or do we defer to the date filters on the activity view to locate the payouts?

---

^ @NihalPartha: Thanks for the input Nihal. I would like to get your feedback on this structure.
@kleinsch: Would like your thoughts... and also the engineering effort associated with any of the components/steps

[mjallday]

@dmdj03 what about existing customers with new bank accounts?

[kleinsch]

In the past I've had problems with giving an interface to correct data. You end up with people correcting the data in the UI, forgetting about it, then coming back to you two weeks later saying "Here's the CSV file I uploaded - why are the payouts different now?"

It wouldn't be too much trouble to give a view of the rows with (including numbers) with invalid rows highlighted, so it's easier for them to correct any problems in the CSV file.

We could probably show them the activity page, filtered to credits. We could use date filters, but that's going to be inexact.

[dmdj03]

@mjallday you mean existing customers without a bank account

[dmdj03]

USE CASE 1: Paying sellers with a customer account and an associated bank account
USE CASE 2: Paying sellers with a customer account but without an associated bank account
USE CASE 3: Paying sellers without a customer account (customer object is created in the process)

USE CASE 1:
Customer ID (required) - Customer ID is the surest way to identify the correct customer in the event that an email address is associated with multiple customers.
Bank Account ID (required) - For customers with more than one bank account
Amount (required)
Appears_on_statement_as (optional)
Internal description (optional)

USE CASE 2:
Customer ID (required)
Bank Account account number (required)
Bank Account routing number (required)
Account holder’s name (required)
Account type (required)
Amount (required)
Appears_on_statement_as (optional)
Internal description (optional)

USE CASE 3:
Customer name (required)
Customer email (required)
Bank Account account number (required)
Bank Account routing number (required)
Account holder’s name (required)
Account type (required)
Amount (required)
Appears_on_statement_as (optional)
Internal description (optional)

[dmdj03]

CSV FIELDS:

Existing customer ID
OR
New customer name
New customer email

---

Existing bank account ID
OR
New Bank account - account number
New Bank account - routing number
New Bank account - holder’s name
New Bank account - type

---

Amount
Appears on statement as
Internal description

[dmdj03]

Flow:

Correctly formatted CSV:

CSV with errors returned:

[kleinsch]

Just to make sure... are these screenshots of the web UI we're going to build or a view of the CSV file from Excel? CSV files can't contain formatting or coloring, they're straight text files. Excel will let you apply formatting, but when you export the file as CSV, all that formatting gets stripped out.

[mjallday]

Will the dashboard do any other validation? E.g. if the sum total of the amounts to pay out is greater than your currently available escrow will the dashboard process the file up until it runs out of money or will it not allow processing of the file to begin in the first place?

This could be a practical application of the knapsack problem if we do the former ;) job interview problems FTW

[zachgersh]

I am interested in implementing either approach but if we go with former what is the user experience when you reach the last few people who can't be paid? Seems to me that the better experience would be to make sure they have enough in their account to pay the full list that way they know the CSV they have put into the system has been 100% fulfilled.

On Oct 12, 2013, at 9:15 AM, Marshall Jones [email protected] wrote:

Will the dashboard do any other validation? E.g. if the sum total of the amounts to pay out is greater than your currently available escrow will the dashboard process the file up until it runs out of money or will it not allow processing of the file to begin in the first place?

This could be a practical application of the knapsack problem if we do the former ;) job interview problems FTW

—
Reply to this email directly or view it on GitHub.

[mjallday]

I agree, it seems much simpler to say "we need to have enough money in the system before we begin processing". Since this is client side processing it's still possible that another operation happens in parallel via the API which drains the available balance so the dashboard will still need to be prepared for that eventuality.

[dmdj03]

@kleinsch This is not the interface; just the spreadsheet. You're right. No coloring. Unless we accept XLS format.
@mjallday We should not process the file if there isn't enough in escrow.

[dmdj03]

"Most of the companies I issue payments to are businesses. I could probably decide on a customer name and customer email, but will those map to the 'Business Name' fields (which is the only field I currently use, aside from banking fields)?"

^Need to add a business field

[dmdj03]

[mahmoudimus]

@mjallday @tarunc @zachgersh can we use http://mailru.github.io/FileAPI/ to build this functionality?

[tarunc]

Nick's built a basic version of this which already reads in the csv and makes requests to complete the payouts. I'll make the final workflow after @dmdj03 completes making the screens.

[mahmoudimus]

@tarunc I took a look @ nick's pull request, it was not ready for merge and was still very MVP with bare features

[tarunc]

Some other projects to consider using:
https://github.com/SheetJS/js-xls
https://github.com/SheetJS/js-xlsx

Excel Interface made here:
http://oss.sheetjs.com/

Since those two are multi-kb large js files, we would need to only load them on this page.

[mahmoudimus]

Might be of note: http://spin.atomicobject.com/2014/01/15/client-side-file-processing-ember-js/

[mjallday]

@cohitre this is the task we were discussing. another engineer started an MVP on #782 but the code base has probably changed a bit since then.

We'd want to create a basic MVP proof-of-concept but in such as way that we could extend it out if it gets traction. Part of that would be evaluating

• if it's a good idea for us to use a third party framework like those mentioned in the issue
• if we should build it as a re-usable component that can be used for other data upload tasks.

A good MVP would also have tests ;)

[cohitre]

Seems like #782 has enough all the file processing that we'd need for an MVP: parsing csv. I don't think a framework will be needed.

It's also implemented as a modal, which is too small for displaying the flow that @dmdj03 mocked up. Probably worth moving this to its own route anyway.

[kyungmin]

Why are we not accepting XLS? Being able to color code and add formatting validation (dates, numbers, dropdown options, etc.) in the spreadsheet would be a huge improvement.

[timnguyen]

@kyungmin not sure, but I think CSV is better for an MVP. From what I've seen, because XLS is a closed format (though I'm speaking from only looking at R and Python libraries) it is correspondingly messy and unreliable to interact with programmatically outside of Visual Basic and on windows so people generally convert the data to CSV to work with anyways. That being said, I know oh so very little about JS.

[tarunc]

I agree with @kyungmin. We should support XLS/X for the final version. I guess for an MVP its less important and we don't need it.

There are already a couple of js libraries to parse XLS/X files:
https://github.com/SheetJS/js-xls
https://github.com/SheetJS/js-xlsx

[dmdj03]

First pass implementation of CSV upload:

[kyungmin]

How long would it take to upload files and submit payouts? Do we need to display loading icons somewhere?

[dmdj03]

Yes

[dmdj03]

[mjallday]

We have deployed and enabled this feature for all test marketplaces. We're giving it a bit of test loving and then we'll be opening it up for production marketplaces shortly afterwards.

[mahmoudimus]

This is amazing. Can we post on the balanced-api issue?

Mahmoud Abdelkader
Co-founder & CTO @ Balanced

On Thu, Feb 27, 2014 at 2:43 PM, Marshall Jones [email protected]:

We have deployed and enabled this feature for all test marketplaces.
We're giving it a bit of test loving and then we'll be opening it up for
production marketplaces shortly afterwards.

Reply to this email directly or view it on GitHubhttps://github.com//issues/21#issuecomment-36301135
.

[mahmoudimus]

I commented on the parent issue.

[rmanisha]

@mjallday When can we add this feature to production marketplaces?

[mjallday]

@rmanisha thanks for reminding me. figuring out the testing strategy here - https://trello.com/c/81gTA179/1511-test-csv-upload

[dmdj03]

We're aiming to release this next week.

[NihalPartha]

awesome!!

On Wed, Apr 23, 2014 at 2:27 PM, Damon Chin [email protected]:

We're aiming to release this next week.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/21#issuecomment-41197126
.

Nihal Parthasarathi
203 645 5151
Co-Founder, CourseHorsehttp://coursehorse.com/?utm_source=Email%2BSignature&utm_medium=Email&utm_content=Email%2BSignature&utm_campaign=Email%2BSignature
Linked In http://www.linkedin.com/in/nihalparthasarathi |
Twitterhttp://twitter.com/#%21/nihalpartha
| Blog http://www.ifnotnowthennever.com/

As Featured in:
WSJhttp://blogs.wsj.com/metropolis/2011/07/22/culture-city-online-a-metropolis-of-learning-all-in-one-place/
| BIhttp://www.businessinsider.com/coursehorse-nyu-stern-startup-winners-katie-kapler-nihal-parthasarathi-2011-7
| Crain's NYhttp://www.crainsnewyork.com/article/20110821/SMALLBIZ/308219987
| TheNextWebhttp://thenextweb.com/apps/2011/07/21/in-nyc-coursehorse-is-changing-the-way-you-search-for-classes/
| Mashable http://mashable.com/2011/07/02/3-new-apps/

[NihalPartha]

Hey Guys,

Checking back in on this - did this get released?

Cheers,

Nihal

On Wed, Apr 23, 2014 at 3:43 PM, Nihal Parthasarathi <[email protected]

wrote:

awesome!!

On Wed, Apr 23, 2014 at 2:27 PM, Damon Chin [email protected]
wrote:

We're aiming to release this next week.

—
Reply to this email directly or view it on GitHub
#21 (comment)
.

Nihal Parthasarathi
203 645 5151
Co-Founder, CourseHorse
http://coursehorse.com/?utm_source=Email%2BSignature&utm_medium=Email&utm_content=Email%2BSignature&utm_campaign=Email%2BSignature
Linked In http://www.linkedin.com/in/nihalparthasarathi | Twitter
http://twitter.com/#%21/nihalpartha | Blog
http://www.ifnotnowthennever.com/

As Featured in:
WSJ
http://blogs.wsj.com/metropolis/2011/07/22/culture-city-online-a-metropolis-of-learning-all-in-one-place/
| BI
http://www.businessinsider.com/coursehorse-nyu-stern-startup-winners-katie-kapler-nihal-parthasarathi-2011-7
| Crain's NY
http://www.crainsnewyork.com/article/20110821/SMALLBIZ/308219987 |
TheNextWeb
http://thenextweb.com/apps/2011/07/21/in-nyc-coursehorse-is-changing-the-way-you-search-for-classes/
| Mashable http://mashable.com/2011/07/02/3-new-apps/

Nihal Parthasarathi
203 645 5151
Co-Founder, CourseHorse
http://coursehorse.com/?utm_source=Email%2BSignature&utm_medium=Email&utm_content=Email%2BSignature&utm_campaign=Email%2BSignature
Linked In http://www.linkedin.com/in/nihalparthasarathi | Twitter
http://twitter.com/#%21/nihalpartha | Blog
http://www.ifnotnowthennever.com/

As Featured in:
WSJ
http://blogs.wsj.com/metropolis/2011/07/22/culture-city-online-a-metropolis-of-learning-all-in-one-place/
| BI
http://www.businessinsider.com/coursehorse-nyu-stern-startup-winners-katie-kapler-nihal-parthasarathi-2011-7
| Crain's NY
http://www.crainsnewyork.com/article/20110821/SMALLBIZ/308219987 |
TheNextWeb
http://thenextweb.com/apps/2011/07/21/in-nyc-coursehorse-is-changing-the-way-you-search-for-classes/
| Mashable http://mashable.com/2011/07/02/3-new-apps/

[mjallday]

@dmdj03 I think we forgot to update this issue, can you please update and close if necessary?

[dmdj03]

@NihalPartha We're doing a soft release to make sure everything is running smoothly. I'll send you a direct link via email to access it.