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

 

[anotheruser]

Thanks, Seems mistake in copy pasting from my appscript to this
2) edited : to incorporate HDFC smartbuy amazon voucher category as it was in newline and failing regex. made it generic.
can copy from above

 

[Satish]

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

This will trigger the script and fetch details every hour.

So how will it look finally?

In first sheet Card number getting populated in merchant column & merchant getting populated into card column.
Line = 66 sequence can be changed

 

[CardGame]

In first sheet Card number getting populated in merchant column & merchant getting populated into card column.
Line = 66 sequence can be changed

Change the column name 😀

 

[ChurningNoob]

In first sheet Card number getting populated in merchant column & merchant getting populated into card column.
Line = 66 sequence can be changed

hahah I never even noticed this till now.

 

[anirban.choudhury]

I think bro in Axis Rewards sheet , Merchant and Card has been swapped , I have updated the code at my end

 

[Max]

I have an interesting problem - @Sidd and @abhigupta - gmail is grouping my axis transaction mails into a conversation (i.e. multiple mails in one thread). Due to this, whenever I get a new txn mail, it causes this script to append all previous 17 transactions (that have already been processed and label axis_processed added to them) again, duplicating these records in the Axis tab of the sheet.




I suspect this is because the label is applied to the conversation thread and not individual mails - I may be wrong but my diagnosis is based on the txns that keep getting duplicated with every run and the mail thread grouped that I see (see screenshot) ; only this set of txn is getting duplicated and appended with every run.

To validate this hypothesis I searched the same string that the code does to detect new Txn emails

"newer_than:1d AND in:inbox AND from:axisbank.com AND subject:Transaction alert AND -label:axis_processed"

As you can see in the screenshot below, that it picks up all the 18 mails (as part of the conversation thread, even though the previous 17 have been processed and marked with the label axis_processed, you can see below) even for the 1 new txn email that has just come in.



Is there a way to fix this please?

 

[Vickymaster1010]

and what if its my yahoo email , how will i track yahoo email ?

 

[ChurningNoob]

I have an interesting problem - @Sidd and @abhigupta - gmail is grouping my axis transaction mails into a conversation (i.e. multiple mails in one thread). Due to this, whenever I get a new txn mail, it causes this script to append all previous 17 transactions (that have already been processed and label axis_processed added to them) again, duplicating these records in the Axis tab of the sheet.




I suspect this is because the label is applied to the conversation thread and not individual mails - I may be wrong but my diagnosis is based on the txns that keep getting duplicated with every run and the mail thread grouped that I see (see screenshot) ; only this set of txn is getting duplicated and appended with every run.

To validate this hypothesis I searched the same string that the code does to detect new Txn emails

As you can see in the screenshot below, that it picks up all the 18 mails (as part of the conversation thread, even though the previous 17 have been processed and marked with the label axis_processed, you can see below) even for the 1 new txn email that has just come in.



Is there a way to fix this please?

Group emails into conversations - Computer - Gmail Help

You can choose whether replies to emails are grouped in conversations, or if each email shows up in your inbox separately. Choose your conversation settings When people reply to an email, G

support.google.com

 

[Max]

Group emails into conversations - Computer - Gmail Help

You can choose whether replies to emails are grouped in conversations, or if each email shows up in your inbox separately. Choose your conversation settings When people reply to an email, G

support.google.com

Thanks for responding @Sidd - I am aware of this option

I was wondering if there is a way to programmatically handle this though (I read the class documentation here, nothing jumped out at me, my programming days are behind me tbh🙈). Conversation based email grouping is really convenient for everything else, so I was hoping I wouldn't have to turn it off.

Thanks again for all the work you have put into this solution, I really appreciate it. If you have the time to look for a solution here, I (and maybe others) would be really thankful.

 

[ChurningNoob]

Thanks for responding @Sidd - I am aware of this option

I was wondering if there is a way to programmatically handle this though (I read the class documentation here, nothing jumped out at me, my programming days are behind me tbh🙈). Conversation based email grouping is really convenient for everything else, so I was hoping I wouldn't have to turn it off.

Thanks again for all the work you have put into this solution, I really appreciate it. If you have the time to look for a solution here, I (and maybe others) would be really thankful.

Ideally conversation threads should not have any impact the code is written to handle individual emails but labeling is something that might cause issue. I will have to check on that

 

[Max]

Ideally conversation threads should not have any impact the code is written to handle individual emails but labeling is something that might cause issue. I will have to check on that

Thank you. My hypothesis is you are labelling correctly, but when a new txn mail comes up on top of the existing grouped and labelled mails, our search query picks up all the pre-existing mails as well as the one new mail.

You can probably validate this by debugging and stepping through the array returned by the search query. Obviously, you know this so much better, so I'll leave it in your hands - thanks for taking the time to look at it (whenever you get a chance) - much appreciated.

 

[Max]

and what if its my yahoo email , how will i track yahoo email ?

@Vickymaster1010 Google allows automation via scripts, hence this was possible. Yahoo doesn't have it so straight forward, so someone will have to write code for it.

A simpler solution you could try would be to create a gmail ID, autoforward all your Axis Txn emails to that ID from your yahoo mail, and then modify the script Sidd shared (the modifications are simple, primarily on sender, subject in the search query) to read from that gmail inbox. I can help if you if this is a solution you are willing to try out.

A more trivial solution is to create a gmail ID for your Axis card so you receive transaction emails there.

 

[anotheruser]

Dont know , but I tested with this grouped conversation and it worked for me.
Maybe there are some settings but I am.not aware of any. Will try sometime

 

[visvin]

@Sidd Found an issue. The axis email is in DDMMYYYY, but the Google sheets is in MMDDYYYY. This is causing the sumif in the Axis Milestone sheet error. Do you know how to fix this?

 

[anotheruser]

@visvin , in preferencea of sheet, you can try changing region to uk

 

[ccboyman]

This is very helpful, and working perfectly. Thanks

 

[karan.rghu]

Thanks, Sidd for this fantastic code.
Have two issues though. Would be really kind of you to rectify,
1. In the milestone page, the total spends shows up as 0 for every month.
2. The code fetches the same transaction multiple times.

 

[ChurningNoob]

1. In the milestone page, the total spends shows up as 0 for every month.

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

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

2. The code fetches the same transaction multiple times.

Even I am trying to debug this issue

 

[karan.rghu]

Change column B for cell B2, then expand to column,
=SUMIF('Axis Rewards'!M:M,[B]TEXT(A2,"mm-yy")[/B],'Axis Rewards'!D:D)


Even I am trying to debug this issue

Hey,

Not too good with excel, can you explain a bit further?

 

[ChurningNoob]

Hey,

Not too good with excel, can you explain a bit further?

My bad.

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