[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?

 

[pranavjain91]

Set it to run once a day(preferably at early morning).
Apart from that, google sheets has an option to remove duplicates.

Data>Data clean-up>Remove duplicates

Man this is super helpful. Didn't know about it. Had been manually deleting repeated entries till now.

 

[CC_Fire]

@Sidd you are a total rockstar, thanks for this and to all the others who have improvised and made this work!!

 

[anoopvk10]

Here is a gyftr tracker that works with axis and hdfc inspired by @Sidd 's work/

gyftr script — Codefile

Create collaborative code files online for your technical interviews, pair programming, teaching, etc.

codefile.io

use above with code,gs, rest all process remains same.
clone doc: https://docs.google.com/spreadsheets/d/1UEReQHew8_6B5BgtqMDq7EKX93L_q2h6RS-tE2xDdqo/edit?usp=sharing
in rate sheet, please add rate per voucher type for other type of vouchers in 'Rate' sheet. I have pre-filled with sample.
Voucher type :
axis: in Email everything between 'buying' and 'gift'
hdfc: Everything between 'buying' and 'from'

Note: Will only work if you have either single card per issuer or multiple cards per issuer but with same reward rates.

PS: Code can be optimized , plus, can add functionality to support multiplier rate change.

@abhigupta getting an error in your implementation -
ReferenceError: parseMessageData is not defined (line 129, file "Code")
Could you please help ?

 

[greenmail007]

Thanks for share.
Much appreciated mate.

 

[phagocyte536]

Brilliant application of coding skills, thanks a lot I love appscript and google sheets, in the starting days of our startup we solely used sheets as DB, scripts as backend, AppSheet as frontend.

 

[cpkalariya]

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

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://gist.github.com/TheSidd/d439779aa048ebcbe9a51ff0514c5751 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?

Great Job. Thumbs Up!!!

 

[miles_guy]

Great scripts. Super useful 👌

 

[anotheruser]

@abhigupta getting an error in your implementation -
ReferenceError: parseMessageData is not defined (line 129, file "Code")
Could you please help ?

code.gs — Codefile

Create collaborative code files online for your technical interviews, pair programming, teaching, etc.

codefile.io

this is working for me since months.
Sheet it works with:

https://docs.google.com/spreadsheets/d/1ZJRY5rT5mrMc35L3BiDWnyiFrLr0mZdwTFGAESQMVWA/edit?usp=sharing

Use with : File -> Settings -> Locale (UK)
Note : Works with multiple gift cards in same email as well

Excel details :
----
bankRate : Set bank rate in bankRate sheet . I have only one card per bank, so for me, rate is card independent. Someone with more cards per bank might need to adjust rate
Rate : Set gift card rewards rate multiplier.
Gyftr: Is populated by script
Gyftr Rewards, calculates, final rewards based upon Transaction type, rate etc.
Month wise : Sums all the points by month-year
-----
Note : If rate changes, it may impact the older data.
I planned earlier to have a date type support but did not get time to implement.
hope this works

 

[pranavjain91]

Has there been a change in the script? It has been working for me since February. But now, entries are getting recorded but the same is not getting reflected in the rewards and milestone worksheet. Any fix?

 

[hrishig]

I am getting mentioned errors in the Excel. Any idea why it is not taking the amount?

 

[anotheruser]

I am getting mentioned errors in the Excel. Any idea why it is not taking the amount?

Seems like first row being empty is causing the issue. Try deleting just 1st row or update formula with if error

 

[hrishig]

I think the error is present in the original excel mentioned in first step. Was the conversion part added later?

• Open excel (https://docs.google.com/spreadsheets/d/1h4Rc5-t5V5irzd3_uzzTDJo5a6QKwiFSI4EIc4mLn84/edit?usp=sharing)

 

[hrishig]

I think the error is present in the original excel mentioned in first step. Was the conversion part added later?

• Open excel (https://docs.google.com/spreadsheets/d/1h4Rc5-t5V5irzd3_uzzTDJo5a6QKwiFSI4EIc4mLn84/edit?usp=sharing)

Yes, the original excel have problem. I simply copy pasted the INR in axis tab from E3 to E2 ( In my own copy) and things started working fine. Adding incase anybody is stuck.

 

[anoopvk10]

I am getting mentioned errors in the Excel. Any idea why it is not taking the amount?

just expand the formula in column F to the entire column

 

[anoopvk10]

Here is a gyftr tracker that works with axis and hdfc inspired by @Sidd 's work/

gyftr script — Codefile

Create collaborative code files online for your technical interviews, pair programming, teaching, etc.

codefile.io

use above with code,gs, rest all process remains same.
clone doc: https://docs.google.com/spreadsheets/d/1UEReQHew8_6B5BgtqMDq7EKX93L_q2h6RS-tE2xDdqo/edit?usp=sharing
in rate sheet, please add rate per voucher type for other type of vouchers in 'Rate' sheet. I have pre-filled with sample.
Voucher type :
axis: in Email everything between 'buying' and 'gift'
hdfc: Everything between 'buying' and 'from'

Note: Will only work if you have either single card per issuer or multiple cards per issuer but with same reward rates.

PS: Code can be optimized , plus, can add functionality to support multiplier rate change.

do we not need the html files mentioned in the original post here ?

 

[anotheruser]

do we not need the html files mentioned in the original post here ?

We need. This script was added over original one

 

[cm9]

Great work

 

[sagar0sammy]

Thank you this is great!

 

[mgforce]

Thanks @Sidd - quite interesting use. Appreciate your efforts to make this available for all.

I don't have Magnus card so no use. But understand members have modified and tweaked for other use cases. Look forward to those

 

[hks789]

Thanks @Sidd - quite interesting use. Appreciate your efforts to make this available for all.

I don't have Magnus card so no use. But understand members have modified and tweaked for other use cases. Look forward to those

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