How to Coda contract management?
Part I | intervals
The idea for this blog came after reading this article (Dutch) about a tool to manage contracts. I thought that a large part could be done as well in Coda. Obviously not everything. Coda does not permit for searching through the contracts (PDF), but besides that, quite a bit is possible. In this serie we explore :
- Intervals & price evolution
- Receiving email notifications
- Integration with your CRM
Setting up your tables
I reuse some of my existing practices in this doc like a link to the invoice settings of the provider. Major companies as Microsoft and Google do not make it easy to find where you can adapt your subscription settings, unless for upgrading. Besides this, I try to keep the doc as lean as possible.
- I want to know in time when a contract is due to decide if I want a renewal or a cancel.
- Besides it is good to know what something costs me. Since I get invoiced in different currencies, I don’t use the currency format, instead I use numbers and via an extra column, I define the currency. More about this in a next blog.
We need something as below. The date is the date of Today() which is Monday Feb 07, 2022. To get the EndDate we used the EU date logic, thus the
IsoWeekDay() . Today is day 7 of week 6 in month 2 of the 1st quarter in 2022. The end of each related period is or today, or the end of this week (Feb 13) or the end of this month (Feb 28) or the end of this quarter (March 31) or one year from today.
Ín case you can cancel or renewal the contract on a daily basis, the date of
Today() is what you need. To get the end of this month we apply an other standard function:
Today().EndofMonth(0) For this day one year later we make use of a formula you also bring into play when you deal with birthdays:
Today().Substitute(Today().Year(),Today().Year()+1)Or you write:Date(
Today().Month() + 12,
The second version is maybe a bit more elegant. Anyway, the next in line in terms of complexity is the end of each quarter based on the assumption that the first quarter of the year includes the first three months. If your quarter starts later, you should adapt accordingly.
You might notice in the above that I follow the standard Coda Date notation which means first the month,second the day. When it comes to calculate with dates I am not so sure that Coda handles other notations well. Better save than sorry.
We are left with the end of the week. We use the
IsoWeekDay() to get there:
Sequence(0,7-Today().IsoWeekday()).Formulamap(Today() + currentvalue).Max()
Although shorter than the previous one, also a bit more obscure. Without
Last() !) you get the range you ask for applying the
FormulaMap() , see below. By the way, you don’t need the Days() but it helps to understand the function faster one moment in time.
The dates based on the interval logica we use later on to set up the automations. If we want updates each week or each month or per quarter (VAT reporting) we have already a part of the logic in place. Likewise for the calculation of our weekly or monthly budget. My intention is to understand how much per month I spend to keep my business floating. I create an inventory of all recurring expenses like Telecom, Tax, Coda, Accounting, Energy etc. All of them have their own frequency, some per year, but most are on a monthly basis. Local services I pay in Euro, the rest via my credit card in USA Dollars.
To understand the contracts and related budgets we‘ll explore how to keep track of changing prices both of the products & services we use as well of the currency exchange rates that apply. Inflation recently became a topic. The set up I designed makes it possible to follow the evolution of your costs, also per service (type).
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter