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

 

[ChurningNoob]

Interesting. Is your script just for Axis Magnus or also for other cards like IDFC, HDFC Card etc.

Can you pls share the code etc?

I started working on a script to capture all card transactions but its turning out to be way complex than I anticipated

 

[rafzy]

Hi Guys
Someone please help me. Followed the steps exactly and I'm getting the values, but still this error pops up. see attached

 

[ChurningNoob]

Hi Guys
Someone please help me. Followed the steps exactly and I'm getting the values, but still this error pops up. see attached

Did you change google sheet link and give permission to it? Also well organized bookmarks do share them looks useful

 

[ak93]

Hi Guys
Someone please help me. Followed the steps exactly and I'm getting the values, but still this error pops up. see attached

I got this error when i didn't include the last part of the url from browser - /edit?*****

 

[rafzy]

Did you change google sheet link and give permission to it? Also well organized bookmarks do share them looks useful

Yes I did give permsission. And ya the bookmarks, just there so that I don't have to run around trying to find something.

 

[rafzy]

Iast part of which URL? the url for the google sheet (I had already included that edit#gid=*************)

 

[ak93]

Iast part of which URL? the url for the google sheet (I had already included that edit#gid=*************)

Yes, the last part of google sheet url. Maybe create a new copy and try again. Also do you have multiple google accounts logged in on the browser? I did the first time and it failed as well.

 

[ak93]

@ChurningSidd whenever I try to remove refunded orders from the excel, I get this to refresh. Any idea why I am not able to save my own file?

 

[ChurningNoob]

@ChurningSidd whenever I try to remove refunded orders from the excel, I get this to refresh. Any idea why I am not able to save my own file?

How are you removing it? Also I noticed that your file is saved on device rather than on cloud why is that?

 

[ak93]

How are you removing it? Also I noticed that your file is saved on device rather than on cloud why is that?

Tried removing comments from row, deleting row, faced same issue. It's in cloud but made available offline

 

[hrishig]

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

Same for me :-(

 

[hrishig]

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.

Can tell more about'use above with code,gs'?

Are you saying add your code in the existing axis tracker project OR create a complete a new project , replace code.gs with your code , copy other two files as is?
For new project creation option, "parseMessageData" function is missing and we have two other functions for Axis and HDFC.

Any clear instructions will help

Found way, commented call in doGet and started calling processTransactionEmails and it started working.

Great efforts! Thank you. !!

 

[saver-user]

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?

will it take all old also or it will start from the day i setup. also my axis is linked to yahoo what do i do?

 

[anotheruser]

Can tell more about'use above with code,gs'?

Are you saying add your code in the existing axis tracker project OR create a complete a new project , replace code.gs with your code , copy other two files as is?
For new project creation option, "parseMessageData" function is missing and we have two other functions for Axis and HDFC.

Any clear instructions will help

Found way, commented call in doGet and started calling processTransactionEmails and it started working.

Great efforts! Thank you. !!

its a separate script. was lazy to merge.

 

[anotheruser]

will it take all old also or it will start from the day i setup. also my axis is linked to yahoo what do i do?

should take old as well . you can create a gmail account and conditionally forward axis emails there

 

[saver-user]

should take old as well . you can create a gmail account and conditionally forward axis emails there

i dont understand but for gmail account it wont take all my old transactions on yahoo right? it will be a fresh start?

 

[saver-user]

"an error occured while saving" what to do now?

 

[saver-user]

s

I tried, but I'm stuck on step 4.

same i am not sure do i replace whole line with link or what> i did everything works but it says error occured while savig

 

[ak93]

s

same i am not sure do i replace whole line with link or what> i did everything works but it says error occured while savig

Not the whole line. Insert the url within openByUrl method.

SpreadsheetApp.openByUrl("insert url here")

 

[mgforce]

It is not that difficult. I will try to update that this weekend.
Check the previous comment for the update I am working on.

Hi - were you able to work on this?