This content originally appeared on Level Up Coding - Medium and was authored by Brian Femiano
This tutorial will use SQLite and Python. All you need to run is Python3 installed locally. By the end you should be able to run the Python script and get the correct output right to the command line.
I wanted to throw together a short tutorial that really shows off how a bit of advanced math can really help data analysis. It’s ok if you don’t completely understand the math behind the implementation. Heck, I’m not an expert either.
My father showed this to me many years ago, and I wanted to write about it because I think it’s cool and it’s Father’s Day.
We want to calculate for potentially millions of bank accounts and hundreds of transactions in each account, what’s the rate of return (interest) given all the activity for each account balance over a one year period.
The naive implementation of rate of return just takes:
((year_end_balance — beginning_balance) / year_end_balance) * 100
and that’s the rate of growth over the year, but this is limited. It doesn’t take into account the inflows and outflows that affect the balance over the course of the year. The simple (well, not so simple) approach for calculating this formula is cumbersome, so much so that the investopedia article on the subject suggests you just use an online calculator to compute rather than try to do it by hand.
Fortunately, there’s a clever way we can implement this using Netwon Raphson method for quadratic convergence.
First let’s create a Python file we can do our analysis with.
From the CLI run:
touch interest_calc.py and open the file in your preferred IDE.
Then our first step is to add the fixture data generation function. The direct github links to these snippets are available periodically in this tutorial, if you’d like to copy and paste the code as we advance.
Direct github link for get_data .
There’s only one unique account id =1 just for clarity of the tutorial, but the code we’re building will work over any number of account ids. Then we add the values for the balance on a particular date. I included the data in date-sorted order just so it’s easier to read, but we’ll see soon that won’t matter.
Now let’s start our calc_time_weighted_interest function to create the database and insert the data so we can query against it:
First we setup a connection to an in-memory transient database. This means the data will evaporate after the execution of our Python script. Then we create a table account_balance to have columns for account_id,date, and balance fields. Lastly, let’s insert our fake data into the table. This is only the first half of the function though.
Now we setup a custom function with the name TIME_WEIGHTED_INTEREST and give it a pointer to thetime_weighted_interest function defined elsewhere. We’ll get to that in a minute.
On the next line we execute a query with the cursor using the contents returned from get_query() , which is where we’ll start writing out SQL. We’ll get to that next.
The cursor fetchall() will return all the results. If the results length is zero, then raise an error. Each result is expected to unpack into two variables account_id and interest . We only have one account id in the fixture data, so just return the first interest result. Finally let’s close the connection.
Direct github link for calc_time_weighted_interest.
Now we’re ready to start writing our query.
Begin the function get_query and let’s start our CTE chain.
First we’d like to add the prior balance to each record. We can do this with a Window function. Partition the data by account_id and look at the rows in date sorted order within each partition. We can use LAG(balance,1) to add the prior balance in the window to each row.
In the next CTE we’ll use the prior_balance to transform each balance into an adjustment delta from the previous record. In the case of the first balance at the beginning of the year, the prior balance will be NULL, so we’ll just set adjustment = balance. Otherwise we set adjustment = balance — prior_balance .
So if we start with for example
date balance prior_balance
2013-01-01 500 NULL
2013-04-11 550 500
No we end up with
date adjustment
2013-01-01 500
2013-04-11 50
Now let’s do another transformation:
In the next CTE, we take the date and transform it as a percentage of the year remaining. This becomes a value between 0.0 and 1.0. Where 0.0 = Dec 31st and 1.0 = Jan 1st.
To do this we take the day of year as an integer, subtract it from 365, then divide that result by 365. Rounded to the nearest 2 decimal places.
So for example day 2013–04–11 is the 100th day of the year.
So (365-100)/365 = 0.73
We consider this the percentage of the year remaining, given the day of the year.
While we’re at it, let’s round our adjustment down to the nearest two decimal places, just for simplicity.
Now to follow our example, the output of this CTE would be:
adjustment perc_year_remaining
500.00 1.00
50.00 0.73
Now let’s do another transformation:
In this CTE let’s sort by account id and within each account_id sort by perc_year_remaining ascending (reverse chronological order for the year). So for example the last adjustment for the year will appear first for each account id, followed by the 2nd to last, etc.
As part of this transformation, we concat the perc_year_remaining with adjustment by pipe | .
The || is SQLite syntax for string concatenation.
So to follow our example, our rows become:
perc_adj_pair
0.73|50.00
1.00|500.00
The reverse chronological sorted output ordering is important for the next CTE step.
Now we GROUP BY account_idand concatenate every pipe-delimited (adjustment, perc_year_remaining)pair into a single string for that account id. Each pair is ;separated. We have to use strings to encode the data as a list, because SQLite unfortunately does not have list/array complex value types.
The result is a single row per account_id with all the adjustment pairs concatenated together in reverse chronological sorted order.
So now we have for account_id = 1
adjustments
0.73|50;1.00|500
Now we can finally call our custom function to calculate the time-weighted interest for all account ids over the year.
Direct github link forget_query
Before we can run the SQL though, we must implement the time_weighted_interest_function
In our function we have to first unpack the string back into a list of (perc_year_remaining, adjustment) pairs. Then we convert back to floating point values for both. We can think of the investments variable as the list of (perc_year_remaining, adjustment) pairs.
Next we want to calculate the balance for the account at the end of the year. We can do this by summing all the adjustments. Since we include as an adjustment the balance at the beginning of the year, this will work. We also remove the final adjustment since it’s not needed to converge on the true time-weighted interest rate.
Our function isn’t done yet though. Next is the juicy part. The convergence function.
We run the convergence function over the investments a max of 25 times to see if we’ve converged to an error below < 0.0000001 . If so we can break out of the retry loop and return the x * 100.0as the interest rate. This is where we need the investments to be in reverse chronological order.
Direct github link for time_weighted_interest
Next let’s call our base function calc_time_weighted_interestto launch and print the result.
Also at the top of the script we should add some imports.
We’re now ready to run the Python script from the CLI.
python calc_interest.py
We should see roughly: 9.4% as the time-weighted interest rate from the single account in our fixture test data.
Level Up Coding
Thanks for being a part of our community! More content in the Level Up Coding publication.
Follow: Twitter, LinkedIn, Newsletter
Level Up is transforming tech recruiting ➡️ Join our talent collective
Calculating time-weighted interest with SQL and the Newton-Raphson method was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Brian Femiano
Brian Femiano | Sciencx (2022-06-23T02:08:36+00:00) Calculating time-weighted interest with SQL and the Newton-Raphson method. Retrieved from https://www.scien.cx/2022/06/23/calculating-time-weighted-interest-with-sql-and-the-newton-raphson-method/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.