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

 

[anirban.choudhuryanirban.choudhury is verified member.]

Change column B for cell B2, then expand to column,

=SUMIF('Axis Rewards'!M:M,TEXT(A2,"mm-yy"),'Axis Rewards'!D:D)

Even I am trying to debug this issue

I think I have fixed this issue at my end
Just doing couple of more testing , will update the fix once done

 

[ChurningNoob]

2. The code fetches the same transaction multiple times.

In final step while creating trigger

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

 

[chiron]

how to filter only axis Magnus card in the script?

 

[Max]

I think I have fixed this issue at my end
Just doing couple of more testing , will update the fix once done

Hi - any luck with the fix here?

 

[Deleted member 5552]

Can anyone please help me if I need to setup for Ace card, where I have to make changes in sheet to get right reward rate?
I am receiving transaction alerts. But it is according to Magnus as you created.

 

[RajBlr]

Can anyone please help me if I need to setup for Ace card, where I have to make changes in sheet to get right reward rate?
I am receiving transaction alerts. But it is according to Magnus as you created.

I believe Ace is more of a Cashback card and doesn't give Edge rewards.

This excel tracking is only for Edge rewards and not CB

 

[Deleted member 5552]

I believe Ace is more of a Cashback card and doesn't give Edge rewards.

This excel tracking is only for Edge rewards and not CB

Yes, but I checked the formula and edited. Now it tracks well. But small problem is Axis Ace has 3 kinds of cashback percentage. So I have to change it manually for some transactions after rewards tracked

 

[004archit]

not working for me after 10 march, any idea why

 

[James2307mr]

not working for me after 10 march, any idea why

Maybe the format of the transaction email got changed. If it is the case, you would have to change the regex code written in the script.

 

[004archit]

Maybe the format of the transaction email got changed. If it is the case, you would have to change the regex code written in the script.

It is working for others?

 

[ChurningNoob]

Working for me.

 

[chiron]

working for me too

 

[Max]

not working for me after 10 march, any idea why

I had a similar problem, on checking I found out that my axis transaction mails were not in the inbox anymore (I am not sure why, maybe gmail is classifying them to be out of inbox). So made a small change on line number 3 i.e. first line of code in function getRelevantMessages() ; as below to remove the check for in:inbox, post which it has been working fine.

var threads = GmailApp.search("newer_than:1d AND from:axisbank.com AND subject:Transaction alert AND -label:axis_processed",0,100)

 

[himanshuhans]

I had a similar problem, on checking I found out that my axis transaction mails were not in the inbox anymore (I am not sure why, maybe gmail is classifying them to be out of inbox). So made a small change on line number 3 i.e. first line of code in function getRelevantMessages() ; as below to remove the check for in:inbox, post which it has been working fine.

It goes to under updates labels

 

[himanshuhans]

What we need to change in order to fetch only for a specific month?

 

[kej]

@Sidd Were you able to find any solution for duplicate entries due to the conversational view being on?

 

[hks789]

@Sidd Were you able to find any solution for duplicate entries due to the conversational view being on?

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

 

[kej]

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

Will do that as well.
For now, I added Google's "Remove Duplicate" functionality in the script itself. So whenever the processing beings the first step it does is to clear any duplicate values in the "Axis" Sheet.

 

[TheMentalist]

Will do that as well.
For now, I added Google's "Remove Duplicate" functionality in the script itself. So whenever the processing beings the first step it does is to clear any duplicate values in the "Axis" Sheet.

Can you share the code for the same

 

[kej]

Can you share the code for the same

Add this function to your script:

/**
 * Removes duplicate rows from the current sheet.
 */
function removeDuplicates() {
  var spreadsheet = SpreadsheetApp.openByUrl("Enter your spreadsheet URL");
  const sheet = spreadsheet.getSheetByName("Axis");
  const data = sheet.getRange(1,1,5000,4).getValues();
  const uniqueData = {};
  for (let row of data) {
    const key = row.join();
    uniqueData[key] = uniqueData[key] || row;
  }
  sheet.clearContents();
  const newData = Object.values(uniqueData);
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

And then just call this removeDuplicates() function in the processTransactionEmails() as the first thing to be done.