• Hey there! Welcome to TFC! View fewer ads on the website just by signing up on TF Community.

[Axis] Automatic credit card transaction and rewards tracker

ChurningNoob

TF Ace
VIP Lounge
RML Group
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 –
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

1671014914488.png

  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

1671015028176.png

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.
1671015078847.png

This will trigger the script and fetch details every hour.

So how will it look finally?

image.png

image.png


1671019779982.png
 

Attachments

  • 1671014816654.png
    1671014816654.png
    14 KB · Views: 98
  • 1671014983955.png
    1671014983955.png
    20.7 KB · Views: 110
Last edited:
Here is a gyftr tracker that works with axis and hdfc inspired by @Sidd 's work/
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 ?
 
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. 🤣
 
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 –
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

View attachment 7902

  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

View attachment 7904

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.
View attachment 7905

This will trigger the script and fetch details every hour.

So how will it look finally?

image.png

image.png


View attachment 7907
Great Job. Thumbs Up!!!
 
@abhigupta getting an error in your implementation -
ReferenceError: parseMessageData is not defined (line 129, file "Code")
Could you please help ?

this is working for me since months.
Sheet it works with:
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
 
Last edited:
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?
 
Here is a gyftr tracker that works with axis and hdfc inspired by @Sidd 's work/
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 ?
 
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
 
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

 
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

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
DateMerchantCardAmount

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

DateMerchantCardAmount
21-05-2023 23:08:288906Mobikwik Gurgaon IN20,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

1685809081404.png
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?
 
Back
Top