I was wondering if it would be possible to do the transactions reconciliation with coda. Let me explain:
For example, you have a table with all the transactions (payments and income in the bank account) and you have another table with the expenses, name of the provider, etc… Will it be possible to have some sort of way to match transactions with the expenses based on the provider and the amount of the transaction?
Then by pressing a button you could set the status of the transaction as “done” or something to know that you´ve checked that you have the bank transaction mapped to the expense.
Yes, it should definitely be possible to build something along these lines in Coda. Here’s an example to start with:
This sounds like a great template, BTW. When you get a version of this built that matches your needs reach out to us and we can look at adding this to our template gallery.
First of all I want thank all the your as well since one of the most nice things of coda is your support.
Let´s digg in to the topic itself: I´ve tested the app you created but I guess I must have explained myself better.
For bank reconciliation here is what we need to look at:
We have 3 tables:
Expenses with the columns: expense date, provider and amount.
Invoices with the columns: invoice date, customer and amount.
Bank transactions with the columns: transaction date, transaction information (sometimes it might now have the provider or the customers name) and amount.
When we try to reconcile to need to take a look at the “bank transactions” table so we can search first for the date of the transaction, second for transaction information and third for the amount. Based on those things we could possibly reconcile transactions.
There are some scenarios we need to consider:
The transaction amount could be a partial payment of an invoice. So the invoice payments will be split in two transactions.
If its a transfer made by you to a provider in the transaction there could be the expense amount plus the transfer cost.
Payments are normally made within a month or if you pay with credit card it can be later but the date is something to consider so you can search in the expenses and invoices table the date and the amount.
For example:
We have an expense of the provider “Coda best app, Inc.” in the date 02/26/2019 and the amount of $ 300.
In our transactions table we have three or four transactions for that amount but two for the amount $ 150 each and the date of this last ones is 27/02/2019 and in the transaction you have “codapp”.
With this information you can tell that the probability of the conciliation is bigger for the two transactions of $ 150, since you´ve the name of the provider and also the date is closer to the expense date.
So the thing will be that in the transactions table we have a column that shows to us the possible invoices (if it´s a positive value) or the the possible expenses (if it´s a negative value) so that when the app does not know the match (since we´re smarter ) to let us decide to which one matches.
Wohoo this is big.
Ok, based on this situation if we solve this out I will certainly say that this is freaking valuable to all the business owners that has to deal with this situations. I know there are services out there that can do this but I actually want to explore coda because I think it could be actually done with it.
So what you’re looking for is to match the expenses to the providers if the provider is blank on the expense and if the total sum of the expenses linked to a provider ‘entry’ doesn’t equal what is expected?
So on 5/20 you log “Provider A” row on table with a value of $300 expected.
Either on 5/20 or another date you log on a separate table two separate expenses of $150.
then you have the two tables on the same page displaying these since they are unlinked -> you then assign “Provider A” to “Expense $150 A” -> “Expense $150 A” falls of the list since it’s linked but “Expense $150 B” still remains and so does “Provider A” sine you’ve only linked $150 of the expected $300. -> then you assign “Provider A” also to “Expense $150 B” -> Finally all fall off the lists because everything has been fully linked.
) to let us decide to which one matches.