• Replies 3 replies
  • Subscribers 961 subscribers
  • Views 41 views
  • Users 0 members are here
Related

Payment Gateway Summary/Detail, daily since beginning of Fiscal Year. How?

John Gerteisen
$organization

Hello,

Finance has asked me to run the Payment Gateway Summary/Detail for each day since the beginning of the fiscal year. Does anyone have an idea of how to provide that information without manually running the report 100+ times?

Much appreciated,

John

Parents
  • $organization

    If it helps anyone in the future:

    I decided to export the detail of each full month. I ran the csv through a python script that sums the amount for items with the same payment_method and batch_type on each date and then exports that info to a new csv. The output may require a touch of clean up, like sorting by date and an occasional weird number, but that is the easy part.

    Code below:

    import pandas as pd

data = pd.read_csv('YOURDATA.csv')

data['amount'] = data['amount'].replace({'\$': '', ',': ''}, regex=True).astype(float)

grouped_data = data.groupby(['batch_type1', 'payment_method', 'transaction_dt'])['amount'].sum().reset_index()

print(grouped_data)

grouped_data.to_csv('grouped_transactions.csv', index=False)

Reply
  • $organization

    If it helps anyone in the future:

    I decided to export the detail of each full month. I ran the csv through a python script that sums the amount for items with the same payment_method and batch_type on each date and then exports that info to a new csv. The output may require a touch of clean up, like sorting by date and an occasional weird number, but that is the easy part.

    Code below:

    import pandas as pd

data = pd.read_csv('YOURDATA.csv')

data['amount'] = data['amount'].replace({'\$': '', ',': ''}, regex=True).astype(float)

grouped_data = data.groupby(['batch_type1', 'payment_method', 'transaction_dt'])['amount'].sum().reset_index()

print(grouped_data)

grouped_data.to_csv('grouped_transactions.csv', index=False)

Children
No Data