Aggregating the $$$ you spend on OMNY by month

README.md

Aggregating the $$$ you spend on OMNY by month

The OMNY dashboard allows you to export Trip History as a CSV. This is a really basic script that aggregates how much you spend on OMNY by month, given the trip-history.csv file you get from OMNY.

Here's how you can run it from the unzipped OMNY download directory:

sqlite3 -init trips.sql trips.db .quit

init.sql

.mode csv
DROP TABLE IF EXISTS trips;
DROP TABLE IF EXISTS trips_temp;
CREATE TABLE trips (
    Ref1 TEXT,
    Ref2 TEXT,
    TripTime DATETIME,
    Mode TEXT,
    Location TEXT,
    ProductType TEXT,
    FareAmount NUMERIC
);
CREATE TABLE trips_temp (
    Ref1 TEXT,
    Ref2 TEXT,
    TripTime DATETIME,
    Mode TEXT,
    Location TEXT,
    ProductType TEXT,
    FareAmount TEXT
);
.import trip-history.csv trips_temp
INSERT INTO trips (Ref1, Ref2, TripTime, Mode, Location, ProductType, FareAmount)
SELECT 
    Ref1,
    Ref2,
    TripTime,
    Mode,
    Location,
    ProductType,
    REPLACE(FareAmount, '$', '') AS FareAmount
FROM trips_temp;
DROP TABLE trips_temp;

SELECT
    strftime('%Y-%m', TripTime) AS Month,
    SUM(FareAmount) AS TotalFareAmount
FROM trips
GROUP BY Month
ORDER BY Month;