[Axis] Automatic credit card transaction and rewards tracker

[ChurningNoob]

TLDR –

• Every hour all credit card transactions are automatically fetched from your email
• Transactions are added to google sheet
• Expected default rewards generated and milestones achievement calculated
• The emails processed are labeled so that there no duplicate transactions
• Project moved here : https://github.com/TheSidd/EmailExpenseTracker

Drawbacks –

• Refunds cannot be calculated as no email
• If email template is changed by the bank, need to modify script

Security & Privacy –

• Main reason to create this and not rely on third-party apps
• Everything remains private in your account

Before you begin –

• Ensure only one google account is logged in the browser, else you need to log out or use incognito
• Open excel (https://docs.google.com/spreadsheets/d/1h4Rc5-t5V5irzd3_uzzTDJo5a6QKwiFSI4EIc4mLn84/edit?usp=sharing)
• Click on File – Make a copy and copy the URL.
• Open https://github.com/TheSidd/EmailExpenseTracker in new tab

Steps –

1. Visit script.google.com in browser where only one google account is logged in
2. Click on Create app scripts
3. Replace Code.gs content with https://gist.github.com/TheSidd/d439779aa048ebcbe9a51ff0514c5751#file-code-gs
4. Paste your copied excel URL in line 62
5. Click Save
6. Left panel Files – Click on Add a file (+ icon) – then HTML – Rename with messages and replace contents from https://gist.github.com/TheSidd/d439779aa048ebcbe9a51ff0514c5751#file-messages-html
7. Left panel Files – Click on Add a file (+ icon) – then HTML – Rename with parsed and replace contents with https://gist.github.com/TheSidd/d439779aa048ebcbe9a51ff0514c5751#file-parsed-html
8. Click Save icon

Now your script should look like this

1. Click on Deploy – New Deployment
2. Now on the pop up click on Setting – select Web App – Provide description and click Deploy
3. Now Authorize access to script, you will receive warning
4. Click on Advance – then Click Go to Project-name (unsafe)
5. Click on allow
6. A URL should be generated, open and you should see your transactions.
7. Click on Save data to Sheet
8. Verify the data in excel

Should look like this, but with data filled



If everything is good so far, we can trigger this every hour.

1. Back to the script page
2. On left panel click on Triggers (Clock icon)
3. Add trigger
4. Select and save the trigger with these options

EDIT : Choose which deployment should run - click dropdown and select your latest deployment.


This will trigger the script and fetch details every hour.

So how will it look finally?

 

[mgforce]

For Indus Debit cards
Use indus-dc.gs for main script

For calculating points
Go to your respective sheet > Extensions > App Scripts
Add code from indus-dc-points.gs to there

Then you can use =calculateIndus6XRewardPoints(eg: B4) in your sheets

GitHub - hariks789/cc-transaction-trackers

Contribute to hariks789/cc-transaction-trackers development by creating an account on GitHub.

github.com

Thanks hks789 for this, but I need some help and guidance here

So, I had app setup as per Sidd's first post here.

Step 1 : I replaced the content in Code.gs (I assume this is the main script) with content from indus-dc.gs
Herein in line 74 I put url for my sheet. Also in the sheet I created a tab called "Transactions" and added in row 1 following headers

Date

Merchant

Card

Amount

Step 2: I then created fresh deployment and it picked up 4 transactions and said saved.
Here the parsed data is shown wrongly, see below

Date	Merchant	Card	Amount
21-05-2023 23:08:28	8906	Mobikwik Gurgaon IN	20,000.00

So issue is that under Merchant header I see card no. and under Card header I see Merchant. But then actual parsing post web app deploy is correct, see this


So - what am I doing incorrectly?

Further for point calculations, you said

Go to your respective sheet > Extensions > App Scripts

So this takes to create new script, it that correct? I did that but then unable to put the formula.

Can you share copy of your spreadsheet with formula applied?

 

[hks789]

Thanks hks789 for this, but I need some help and guidance here

So, I had app setup as per Sidd's first post here.

Step 1 : I replaced the content in Code.gs (I assume this is the main script) with content from indus-dc.gs
Herein in line 74 I put url for my sheet. Also in the sheet I created a tab called "Transactions" and added in row 1 following headers

Date

Merchant

Card

Amount

Step 2: I then created fresh deployment and it picked up 4 transactions and said saved.
Here the parsed data is shown wrongly, see below

Date	Merchant	Card	Amount
21-05-2023 23:08:28	8906	Mobikwik Gurgaon IN	20,000.00

So issue is that under Merchant header I see card no. and under Card header I see Merchant. But then actual parsing post web app deploy is correct, see this

View attachment 18941
So - what am I doing incorrectly?

Further for point calculations, you said

So this takes to create new script, it that correct? I did that but then unable to put the formula.

Can you share copy of your spreadsheet with formula applied?

You can ignore header difference. I use a different spreadsheet thats why you are seeing things a little different.
You can change things as per your need.
My sheet is bit more complicated with few other changes, I will try to strip them out and share.
Share the error you are facing in DM, I will take a look

 

[mgforce]

You can ignore header difference. I use a different spreadsheet thats why you are seeing things a little different.
You can change things as per your need.
My sheet is bit more complicated with few other changes, I will try to strip them out and share.
Share the error you are facing in DM, I will take a look

Thanks for the efforts. I googled a bit and was able to make formula work. Now I need to check if get points as per this 🙂

Is there an easy guide on how to make .gs script. thinking where else can I apply these scripts to make life easier 😛

 

[mgforce]

My sheet is bit more complicated with few other changes, I will try to strip them out and share.

It will be helpful to get you sheet because for me, I only have spends parsed in excel and then I apply function "calculateIndus6XRewardPoints"

Over DM sharing my file, so that you can see..

 

[hks789]

Thanks for the efforts. I googled a bit and was able to make formula work. Now I need to check if get points as per this 🙂

Is there an easy guide on how to make .gs script. thinking where else can I apply these scripts to make life easier 😛

ChatGPT can help you to a great extend

 

[forest_gump]

Thanks sid. It is very useful 😃.

 

[mgforce]

ChatGPT can help you to a great extend

Not sure, but will try!

 

[ChurningNoob]

For Indus Debit cards
Use indus-dc.gs for main script

For calculating points
Go to your respective sheet > Extensions > App Scripts
Add code from indus-dc-points.gs to there

Then you can use =calculateIndus6XRewardPoints(eg: B4) in your sheets

GitHub - hariks789/cc-transaction-trackers

Contribute to hariks789/cc-transaction-trackers development by creating an account on GitHub.

github.com

Hey! Thanks for picking it up.

I was trying to create a single script to track all bank cc and add in different excel tabs, I will use your regex

 

[ChurningNoob]

Now that I see there is some interest in this script, I will try to improve and add support for more banks.

Feel free to contribute @abhigupta @hks789

GitHub - TheSidd/EmailExpenseTracker

Contribute to TheSidd/EmailExpenseTracker development by creating an account on GitHub.

github.com

 

[hks789]

Now that I see there is some interest in this script, I will try to improve and add support for more banks.

Feel free to contribute @abhigupta @hks789

GitHub - TheSidd/EmailExpenseTracker

Contribute to TheSidd/EmailExpenseTracker development by creating an account on GitHub.

github.com

I do have IDFC and SBI ones, I will add them shortly

 

[mgforce]

Now that I see there is some interest in this script, I will try to improve and add support for more banks.

Feel free to contribute @abhigupta @hks789

GitHub - TheSidd/EmailExpenseTracker

Contribute to TheSidd/EmailExpenseTracker development by creating an account on GitHub.

github.com

This will be really great. Look forward to the same.

 

[foden]

Tried for Magnus Axis, did all the steps successfully, but the sheet is empty. Pls guide

 

[kapoorsanyam]

@ChurningSidd seems like the magnus points tracker ("Axis Tracker") has stopped loading the data since yesterday. My last update on sheet was yesterday 12:36 am. Can you please have a look if yours is working ? If not, can you please fix it and update here. That would be a great help !! Thanks !!
PS: I've tried doing the whole process again but I end up with empty parsed data. Seems like an issue with parsing but I might be wrong.

 

[karan.rghu]

@ChurningSidd Not working for me as well

@ChurningSidd seems like the magnus points tracker ("Axis Tracker") has stopped loading the data since yesterday. My last update on sheet was yesterday 12:36 am. Can you please have a look if yours is working ? If not, can you please fix it and update here. That would be a great help !! Thanks !!
PS: I've tried doing the whole process again but I end up with empty parsed data. Seems like an issue with parsing but I might be wrong.

 

[mgforce]

I do have IDFC and SBI ones, I will add them shortly

Look forward.

I want to track IDFC Credit Card spends. Can share email format if it helps?

 

[hks789]

Look forward.

I want to track IDFC Credit Card spends. Can share email format if it helps?

Axis and IDFC card updates are added to the repo.

GitHub - hariks789/cc-transaction-trackers

Contribute to hariks789/cc-transaction-trackers development by creating an account on GitHub.

github.com

 

[sagar0sammy]

@ChurningSidd Not working for me as well

Getting this error, lets see if we can fix this.

 

[vigneshn92]

Getting this error, lets see if we can fix this.
View attachment 19637

I replaced getPlainBody() with getBody(); in parseMessageData function and it worked for me. It looks like there is some issue when parsing it with the other function since the output from the previous mail versus the ones after 8th april seemed a bit different. To re process old data, I had to remove the labels axis_processed manually from gmail for the transactions from the last 3-4 days and the transactions were read fine and written to the excel sheet. (also change the condition newer_than:1d to newer_than:4d to process old data as a one time activity.)

 

[mgforce]

Axis and IDFC card updates are added to the repo.

GitHub - hariks789/cc-transaction-trackers

Contribute to hariks789/cc-transaction-trackers development by creating an account on GitHub.

github.com

Thanks a ton for this.

2 questions in general for script:
1) Can we add multiple scripts to same Google Scripts Project?
2) If yes, we need to create deployment again and again, once new script is added right?

Assuming yes for 1 above, I added new script called idfc-cc.gs and copied the raw data from here between { } right in below?



Prior to this, I had copied the raw data from here and replace the full text but got below error

 

[hks789]

I replaced getPlainBody() with getBody(); in parseMessageData function and it worked for me. It looks like there is some issue when parsing it with the other function since the output from the previous mail versus the ones after 8th april seemed a bit different. To re process old data, I had to remove the labels axis_processed manually from gmail for the transactions from the last 3-4 days and the transactions were read fine and written to the excel sheet. (also change the condition newer_than:1d to newer_than:4d to process old data as a one time activity.)

Check my code with additional steps to remove line breaks with email body. It should work for both old and new emails. Let me know if you still face the issue, I will update accordingly.