Giacenza Media Annua for Revolut accounts

revolut-gma.bash

#!/bin/bash

# Compute the Giacenza Media Annua (GMA) of a Revolut account, required by the Italian INPS.

# Dependencies: awk, GNU date, getopts, xargs

# Computation method: https://bit.ly/3avDLu3
# Thread on Revolut forum: https://bit.ly/3511e5h

# Assumptions:
# 1. Dates are in YYYY-MM-DD format. The Revolut app uses your locale, so if this is set
#    to it-IT, then this assumption is already satisfied. Otherwise, convert the dates in
#    your CSV to ISO 8601 before using the script.
# 2. The time interval considered is between January 1 and December 31 of the same year
#    (since it is an annual value).
# 3. Transactions are in ascending date order.
# 4. The actual balance of day D is the last registered during D. This is because the GMA
#    is a weighted arithmetic mean, and the entries of the CSV are sorted by date. Hence,
#    if T is an array of transactions registered during D, with |T| > 1, then all entries
#    in T except the last one have null weight, because they have been on the account for
#    less than one day. Thus, they do not affect the GMA, and can be safely skipped.

version="2022.05"

completed_date=4
balance=10
separator=","
use_decimal_point=false

print_usage() {
    printf "Usage: `basename "$0"`  [options]\n%-2sOptions:\n"
    printf "%-4s-c \n%-6s'Completed Date' column number\n"
    printf "%-6sDefault: $completed_date\n"
    printf "%-4s-a \n%-6s'Balance' column number\n%-6sDefault: $balance\n"
    printf "%-4s-s \n%-6sSeparator character of the input CSV\n"
    printf "%-6sDefault: '$separator'\n"
    printf "%-4s-d \n%-6sUse point instead of comma as decimal separator\n"
    printf "%-6sDefault: $use_decimal_point\n"
}

if [[ $# -lt 1 ]]
then
    print_usage
    exit 1
fi

while getopts "cbs:dvh" flag; do
    case "${flag}" in
        c) completed_date="${OPTARG}" ;;
        b) balance="${OPTARG}" ;;
        s) separator="${OPTARG}" ;;
        d) use_decimal_point=true ;;
        v) printf "Version: $version\n"
           exit 0 ;;
        *) print_usage
           exit 0 ;;
    esac
done

result=0
prev_date=""
year=""

get_days() {
    # input values are in seconds, and there are 60 * 60 * 24 = 86400 seconds in a day
    days=$((($1 - $2) / 86400))
    days=${days#-}
    days=$(($days + 1))
    printf $days
}

mycmd="awk -F '$separator' '(!unique[\$$completed_date]++ && NR > 1) {print \$$completed_date,\$$balance}' '$1'"

while read curr_date
do
    d1=""
    d2s=""

    if [[ ! $prev_date ]]
    then
        year=$(date -d "${curr_date% *}" "+%Y")
        d1=$(date -d "$year-01-01" +%s)
        d1s="1 Jan $year"
        prev_date="$year-01-01"
        printf "Calculating the Giacenza Media Annua of the year $year\n\n"
    fi

    if [[ ! $d1 ]]
    then
        d1=$(date -d "${prev_date%% *}" +%s)
    fi

    d2=$(date -d "${curr_date%% *}" +%s)
    days="$(get_days $d1 $d2)"

    money=`echo ${curr_date##* } | xargs`
    # approximated to 2 decimal values for ease of reading, comment to see exact values
    money=$(awk "BEGIN {printf(\"%.2f\", $money); exit}")
    p=$(awk "BEGIN {print $money*$days; exit}")
    result=$(awk "BEGIN {print $result+$p; exit}")

    d1s=`echo ${prev_date%% *} | xargs`
    if [[ ! $d2s ]]
    then
        d2s=`echo ${curr_date%% *} | xargs`
    fi

    printf "$d1s to $d2s: $money x $days = $p\n"

    prev_date=$curr_date
done <<< `eval $mycmd`

if [[ ! "$d2s" = "$year-12-31" ]] # last date is not the last day of the year
then
    days="$(get_days $(date -d "$year-12-31" +%s) $d2)"
    p=$(awk "BEGIN {print $money*$days; exit}")
    result=$(awk "BEGIN {print $result+$p; exit}")
    printf "$d2s to $year-12-31: $money x $days days = $p\n"
fi

result=`awk "BEGIN {print $result/365; exit}"`

if !$use_decimal_point
then
    result=${result/\./\,}
fi

if [[ "$result" -lt 0 ]] # if the GMA is negative, the value to declare must be 0
then
    result_ceil=0
else
    result_ceil=${result%.*}
    result_ceil=$((result_ceil + 1))
fi

printf "\nResult: $result, to be rounded up to $result_ceil\n"

exit 0

@lcpz I think this file is dangerous as it may make serious errors.

First, note that:
I get the error revolut-gma.bash: line 123: !false: command not found revolut-gma.bash: line 128: [[: 895.307: syntax error: invalid arithmetic operator (error token is ".307")
at the end of the file. Can this be ignored?

Second, I get the error awk: cmd. line:1: BEGIN {printf("%.2f", ); exit} awk: cmd. line:1: ^ syntax error awk: cmd. line:1: BEGIN {print *2; exit} awk: cmd. line:1: ^ syntax error awk: cmd. line:1: BEGIN {print 200708+; exit} awk: cmd. line:1: ^ syntax error
This is because part of the transactions are in Product==Savings instead of Product==Current. In this account, I had some 0s, which led the program to use these 0s for calculation (as it uses the first entry on a day). I do not know how to interpret the error (I do not know bash).

Third, I used 3 methods and get 3 results (if i ignore the above errors anyway). For each csv file, I note what the first two lines look like:

• using the original EUR statement.csv, I see the dates in excel are not sorted. The Ubuntu reader reads dates as 2022-01-01 to 2022-06-13: 100.00 x 163 = 16300 || 2022-06-13 to 2022-01-02: 7.79 x 162 = 1261.98.
• after I sort this statement.csv using Excel in ascending order, the file reads dates as 2022-01-01 to 1/2/2022: 7.79 x 2 = 15.58 || 1/2/2022 to 1/3/2022: 107.79 x 2 = 215.58
• due to this date format change (which appears to happen automatically in Excel when sorting), I change the format again to YYYY-MM-DD, and get 2022-01-01 to 2022-06-13: 100.00 x 163 = 16300 || 2022-06-13 to 2022-01-02: 7.79 x 162 = 1261.98.
  These three methods give amounts 896, 660 and 551, respectively.

Thanks for the effort of writing this file, but I think caution is needed or patches should be applied. I am sorry I cannot help further, as I have no idea about bash or this coding language.