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

 

[hks789]

Thanks a ton for this.

2 questions in general for script:
1) Can we add multiple scripts to same Google Scripts Project?
2) If yes, we need to create deployment again and again, once new script is added right?

1 question for IDFC script, the raw data copied from here need to be added between { } right in below?

View attachment 19645

If you are comfortable doing it, the same code will work in a single project, but you will need to do additional tweaking.
Currently, they are separate projects with separate deployments.
I have moved every variable to the top of the code to eventually make it a single one.

For now, you need to make it separate.
You need to replace the entire content of the code.gs file with my gs file.

Right now, I am working on making anniversary and financial year totals of each bank/card. Will update once Sidd post the common code.
Little sneak peak

 

[mgforce]

If you are comfortable doing it, same code will work in a single project but you need to do additional tweaking.

Is additional tweaking difficult? Can you guide a bit?

For now, creating a separate project and separate deployment

 

[hks789]

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

 

[mgforce]

Is additional tweaking difficult? Can you guide a bit?

For now, creating a separate project and separate deployment

Thanks @hks789. Created separate project. But one issue...

I have email setting as "Conversation view on". Herein I see that multiple emails within one conversation are not read for IDFC. Any hack for that?

 

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

Wow - saw the sneak peak. It is going to be fantastic !

 

[sagar0sammy]

@ChurningSidd seems like the magnus points tracker ("Axis Tracker") has stopped loading the data since yesterday. My last update on sheet was yesterday 12:36 am. Can you please have a look if yours is working ? If not, can you please fix it and update here. That would be a great help !! Thanks !!
PS: I've tried doing the whole process again but I end up with empty parsed data. Seems like an issue with parsing but I might be wrong.

Updated code here:

code.gs

GitHub Gist: instantly share code, notes, and snippets.

gist.github.com

 

[hks789]

Thanks @hks789. Created separate project. But one issue...

I have email setting as "Conversation view on". Herein I see that multiple emails within one conversation are not read for IDFC. Any hack for that?

I used to run the code daily only once at 5AM. Seems to work for me.

 

[hks789]

Updated code here:

code.gs

GitHub Gist: instantly share code, notes, and snippets.

gist.github.com

Its better to remove all line breaks, html special font tags etc before matching with regex

 

[mgforce]

I used to run the code daily only once at 5AM. Seems to work for me.

I had 3 conversations at the same day, 3 days prior. For the same it is not reading.

When I manually run the code, it is showing below, which matches as total 4 (one conversation) +4 (one conversation) +1 transaction mails, see screenshot of same search from mail box.





But when running deployment, only one read - the last one.

So wondering where am doing wrong.. Something to do with parsing?

 

[hks789]

I had 3 conversations at the same day, 3 days prior. For the same it is not reading.

When I manually run the code, it is showing below, which matches as total 4+4+1 transaction mail

View attachment 19650

View attachment 19651

But when running deployment, only one read - the last one.

So wondering where am doing wrong..

Your deployment might be running too frequently. Whats the current duration.

NB: When you make some change to the code you need to update the deployment with latest version.

Manage deployments> pencil ✏️ button > new version

 

[mgforce]

Your deployment might be running too frequently. Whats the current duration

I am manually running the deployment.

I just created project today, when I quoted and then was testing it. Not yet saved data, just parsing...

 

[hks789]

I am manually running the deployment.

I just created project today, when I quoted and then was testing it. Not yet saved data, just parsing...

Thats weird. If you have already removed the tag, the manual run and deployment will make no difference.

 

[mgforce]

Thats weird. If you have already removed the tag, the manual run and deployment will make no difference.

Tag? I didn't remove any tag etc anywhere so far

 

[hks789]

Tag? I didn't remove any tag etc anywhere so far

If the code run once, the mails associated in that run will be marked with a tag/label.
In the next run the mails with same existing tags/labels will be ignored.
You need to remove tags/labels from your emails for the script to parse it again.

 

[sagar0sammy]

Its better to remove all line breaks, html special font tags etc before matching with regex

Yes I will start using your codebase soon, thanks!

 

[kapoorsanyam]

Updated code here:

code.gs

GitHub Gist: instantly share code, notes, and snippets.

gist.github.com

Hey thanks man !! Good catch @hks789 !! I had fixed the code by removing the line breaks but forgot to update it here yesterday. Remembered it today and saw already people have fixed it. Good community !! And certainly good developers 😛

 

[mgforce]

If the code run once, the mails associated in that run will be marked with a tag/label.
In the next run the mails with same existing tags/labels will be ignored.
You need to remove tags/labels from your emails for the script to parse it again.

Ok - you mean label. Yes - that I did remove from mails.

But still conversation view threads not being read.

 

[vigneshelan]

Hey guys, I got my Magnus card last month and I'm new to miles/reward points so I used to calculate the points manually every time. I'm not the excel person so I created a simple app that calculates and saves the points. There is a tracker to view transactions and the ability to upload and download them as a CSV. I request you guys to please give it a try and let me know your feedback. I'm planning to add more features in the coming days.

RP Calculator: https://rpcalculator.vercel.app/

PS: I would like to create it as a Post but I don't have the privilege to do that. cc: @TechnoFino

 

[hks789]

Hey guys, I got my Magnus card last month and I'm new to miles/reward points so I used to calculate the points manually every time. I'm not the excel person so I created a simple app that calculates and saves the points. There is a tracker to view transactions and the ability to upload and download them as a CSV. I request you guys to please give it a try and let me know your feedback. I'm planning to add more features in the coming days.

RP Calculator: https://rpcalculator.vercel.app/

PS: I would like to create it as a Post but I don't have the privilege to do that. cc: @TechnoFino

Nice job.
One suggestion - Can we add a check box as well for the tracker to let the user to toggle checked(When one verifies actual credit)

 

[ChurningNoob]

Hey guys, I got my Magnus card last month and I'm new to miles/reward points so I used to calculate the points manually every time. I'm not the excel person so I created a simple app that calculates and saves the points. There is a tracker to view transactions and the ability to upload and download them as a CSV. I request you guys to please give it a try and let me know your feedback. I'm planning to add more features in the coming days.

RP Calculator: https://rpcalculator.vercel.app/

PS: I would like to create it as a Post but I don't have the privilege to do that. cc: @TechnoFino

Can you please DM? We can collab to make my script as a webapp