Paypal transactions API retrieval → JSON → CSV → hledger-flow import → hledger import rules

1-paypalcsv

#!/usr/bin/env bash

# Download last 30 days of transaction history from Paypal as CSV.
# Adapted from Simon Michael: gist.github.com/simonmichael/62cc9661569cdea990dc2020ac36582a

# REQUIREMENTS:
# brew install jsonpp jq
# npm install -g json2csv

# USAGE:
# paypalcsv # retrieves transactions since beginning of the current month
# paypalcsv 2021-05-01 2021-05-31 # max 31 days

# credentials from an API app created in https://developer.paypal.com
# $PAYPAL_CLIENT_ID
# $PAYPAL_SECRET
# stored in environment variables

if [ -n "$1" ] && [ -n "$2" ]
then
    # takes first parameters as date range
    START=$(gdate +%Y-%m-%dT00:00:00.000Z --date "$1")
    END=$(gdate +%Y-%m-%dT23:59:59.999Z --date "$2")
    # max date range is 31d
else
    # defaults to transactions since the beginning of the current month until present day
    START=$(date +%Y-%m-01T00:00:00.000Z)
    END=$(date -v+1d +%Y-%m-%dT00:00:00.000Z)
fi

TOKEN=$(curl -s https://api-m.paypal.com/v1/oauth2/token \
  -H "Accept: application/json" \
  -H "Accept-Language: en_US" \
  -u "$CLIENT_ID:$SECRET" \
  -d "grant_type=client_credentials" | jq .access_token -r)

# max page size is 500, so this will fetch at most 500 transactions.
# sed strips the field name prefixes, while keeping them unique
curl -s -H "Authorization: Bearer $TOKEN" "https://api.paypal.com/v1/reporting/transactions?start_date=$START&end_date=$END&fields=all&page_size=500&page=1" | jq -c '.transaction_details'

2-retrieve

#!/usr/bin/env bash

# Runs the retrieval process for Paypal (and others)

LEDGER_ROOT="~/ledger"
CURRENT_YEAR=$(date +%Y)
CURRENT_MONTH=$(date -u +"%Y-%m")
PREVIOUS_MONTH=$(date -v -1m +%Y-%m)

# Export transactions of current month in a YEAR-MO_Paypal.json file
paypalcsv >| $LEDGER_ROOT/import/owner/paypal/1-in/$CURRENT_YEAR/$CURRENT_MONTH_Paypal.json

# Export transactions of previous month in a YEAR-MO_Paypal.json file
paypalcsv ${PREV_MONTH}-01 $(gdate +%Y-%m-%dT00:00:00.000Z --date "${PREV_MONTH}-01 +1month -1day") >| $LEDGER_ROOT/import/owner/paypal/1-in/$CURRENT_YEAR/${PREV_MONTH}_Paypal.json

3-preprocess

#!/usr/bin/env bash

# Preprocess the Paypal JSON into a CSV
# will be run by hledger-flow import (https://github.com/apauley/hledger-flow)
# this file has to be placed in import/owner/paypal

jq '.transaction_details[] | { 
    transaction_id: .transaction_info.transaction_id,
    paypal_reference_id: .transaction_info.paypal_reference_id,
    paypal_reference_id_type: .transaction_info.paypal_reference_id_type,
    event_code: .transaction_info.transaction_event_code,
    initiation_date: .transaction_info.transaction_initiation_date,
    updated_date: .transaction_info.transaction_updated_date,
    status: .transaction_info.transaction_status,
    subject: .transaction_info.transaction_subject,
    note: .transaction_info.transaction_note,
    invoice_id: .transaction_info.invoice_id,
    currency: .transaction_info.transaction_amount.currency_code,
    amount: .transaction_info.transaction_amount.value,
    fee: .transaction_info.fee_amount.value,
    shipping: .transaction_info.shipping_amount.value,
    balance_currency: .transaction_info.ending_balance.currency_code,
    balance_amount: .transaction_info.ending_balance.value,
    avail_balance_currency: .transaction_info.available_balance.currency_code,
    avail_balance_amount: .transaction_info.available_balance.value,
    custom: .transaction_info.custom_field,
    protection_eligibility: .transaction_info.protection_eligibility,
    payer_id: .payer_info.account_id,
    payer_email: .payer_info.email_address,
    payer_phone: (.payer_info.phone_number.country_code + " " + .payer_info.phone_number.national_number),
    payer_addr_status: .payer_info.address_status,
    payer_full_name: .payer_info.payer_name.alternate_full_name,
    payer_country: .payer_info.country_code,
    payer_addr_line1: .payer_info.address.line1,
    payer_addr_line2: .payer_info.address.line2,
    payer_addr_city: .payer_info.address.city,
    payer_addr_country: .payer_info.address.country_code,
    payer_addr_postal: .payer_info.address.postal_code,
    shipping_name: .shipping_info.name,
    item1_name: .cart_info.item_details[0].item_name,
    item1_descr: .cart_info.item_details[0].item_description,
    item1_quantity: .cart_info.item_details[0].item_quantity,
    item1_total_amount: .cart_info.item_details[0].total_item_amount.value,
    item1_currency: .cart_info.item_details[0].total_item_amount.currency_code,
    item1_invoice_number: .cart_info.item_details[0].invoice_number 
}' "$1" | json2csv >| "$2"

4-paypal.rules

# Paypal transactions processing rules, for Paypal API JSON>CSV prepared by preprocess-paypal

; Sample data
; "transaction_id","paypal_reference_id","paypal_reference_id_type","event_code","initiation_date","updated_date","status","subject","note","invoice_id","currency","amount","fee","shipping","balance_currency","balance_amount","avail_balance_currency","avail_balance_amount","custom","protection_eligibility","payer_id","payer_email","payer_phone","payer_addr_status","payer_full_name","payer_country","payer_addr_line1","payer_addr_line2","payer_addr_city","payer_addr_country","payer_addr_postal","shipping_name","item1_name","item1_descr","item1_quantity","item1_total_amount","item1_currency","item1_invoice_number"
; "xxxxxxxxxxxxxxxx","X-XXXXXXXXXXXXXXXXX","PAP","T0003","2021-06-01T15:05:16+0000","2021-06-01T15:05:16+0000","S",,,"xxxxxxxxxxxxxxxxxxxxx","EUR","-1.00",,,"EUR","10.00","EUR","10.00",,"02","U11XXXXXXXXXXXXX","nytimes-paypal@nytimes.com"," ","N","The New York Times Co.","US","X Street 1","","Paris","FR","75000","John, Doe",,,"1","1.00","EUR","xxxxxxxxxxxxxxxxxxxxx"

skip  1

fields transaction_id, paypal_reference_id, paypal_reference_id_type, event_code, initiation_date, date, paypal_status, subject, note, invoice_id, cur, amount1, fee, shipping, balance_currency, balance, avail_balance_currency, avail_balance_amount, custom, protection_eligibility, payer_id, payer_email, payer_phone, payer_addr_status, payer_full_name, payer_country, payer_addr_line1, payer_addr_line2, payer_addr_city, payer_addr_country, payer_addr_postal, shipping_name, item1_name, item1_descr, item1_quantity, item1_total_amount, item1_currency, item1_invoice_number

date-format  %Y-%m-%dT%H:%M:%S%z

description  %subject %payer_full_name
comment      %note %item1_name, event:%event_code, payerID:%payer_id, email:%payer_email, country:%payer_country, ref:%paypal_reference_id_type %paypal_reference_id, transactionID:%transaction_id, invoice:%invoice_id

if %paypal_status P # (pending)
  skip
if %paypal_status S
  status     *

if %cur USD
  currency   $
if %cur EUR
  currency   €

account1     assets:paypal

if %amount1 -
  account2  expenses:unknown
if %amount1 ^[0-9]
  account2  income:unknown

# EVENT CODES
# see https://developer.paypal.com/docs/integration/direct/transaction-search/transaction-event-codes/
#
# T00nn PayPal account-to-PayPal account payment
# T01nn Non-payment-related fees
# T01nn Non-payment-related fees
# T02nn Currency conversion
# T03nn Bank deposit into PayPal account
# T04nn Bank withdrawal from PayPal account
# T05nn Debit card
# T06nn Credit card withdrawal
# T07nn Credit card deposit
# T08nn Bonus
# T09nn Incentive
# T10nn Bill pay
# T11nn Reversal
# T12nn Adjustment 
# T13nn Authorization
# T14nn Dividend
# T15nn Hold for dispute or other investigation
# T16nn Buyer credit deposit
# T17nn Non-bank withdrawal
# T18nn Buyer credit withdrawal
# T19nn Account correction
# T20nn Funds transfer from PayPal account to another
# T21nn Reserves and releases
# T22nn Transfers
# T30nn Generic instrument and Open Wallet
# T50nn Collections and disbursements
# T97nn Payables and receivables
# T98nn Display only transaction
# T99nn Other

if
%event_code T15
%event_code T1105 # (hold reversal)
  account2     assets:receivables:onhold
  description  Account Hold