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

 

[Akash369]

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?

Appreciate your work! Working perfectly.

 

[mastfun]

@ChurningSidd Great work !!! Let me know if u need any help in coding! And I think the Card and Merchant columns has to be swapped

 

[saver-user]

1. Click on Advance – then Click Go to Project-name (unsafe)
2. Click on allow

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

SpreadsheetApp.openByUrl("insert url here")

thanks bro it worked

 

[saver-user]

Now its not calculating rewards..

 

[ak93]

Now its not calculating rewards..

Currency converter is already part of the excel formula is present in column F, you just need to drag it to fill other rows once data is populated

You need to do this

 

[saver-user]

You need to do this

Hii brother i still dont understand. i dont know anything about excel

 

[Bharat]

@ChurningSidd Created a PR to fetch the HDFC CC data
to test : need to create a sheet named HDFC in the same spreadsheet, and currently the regex is for 1 day and only Rs (INR) supported, need more currency email message samples to change the regex
Do let me know if it works as expected and/or needs any change.
if the code looks bad, pls feel free to add your commits, I just did it in a jiffy.

 

[ChurningNoob]

@ChurningSidd Great work !!! Let me know if u need any help in coding! And I think the Card and Merchant columns has to be swapped

Project is in github feel free to contribute and make a PR

 

[ChurningNoob]

@ChurningSidd Created a PR to fetch the HDFC CC data
to test : need to create a sheet named HDFC in the same spreadsheet, and currently the regex is for 1 day and only Rs (INR) supported, need more currency email message samples to change the regex
Do let me know if it works as expected and/or needs any change.
if the code looks bad, pls feel free to add your commits, I just did it in a jiffy.

I dont have hdfc hence never added those scripts but @hks789 has some scripts if he is ok he can add those my project

 

[hks789]

I dont have hdfc hence never added those scripts but @hks789 has some scripts if he is ok he can add those my project

Feel free to pick anything from my repo: @ChurningSidd @Bharat

GitHub - hariks789/cc-transaction-trackers

Contribute to hariks789/cc-transaction-trackers development by creating an account on GitHub.

github.com

These scripts need minor changes to the sheets to work, Some needs additional changes, as mentioned in the ReadMe
Rest all are same.

 

[hks789]

Hi - were you able to work on this?

Was busy last week. I will try to do this this coming weekend.
The plan is to do it for all supported scripts.

 

[Bharat]

Feel free to pick anything from my repo: @ChurningSidd @Bharat

GitHub - hariks789/cc-transaction-trackers

Contribute to hariks789/cc-transaction-trackers development by creating an account on GitHub.

github.com

These scripts need minor changes to the sheets to work, Some needs additional changes, as mentioned in the ReadMe
Rest all are same.

Did not know about this, will check but looks nice and clean

 

[ambrewz]

Now its not calculating rewards..

In AXIS Tab(1st tab) copy entire values of Column D (From D3, D4, D5.... onwards) to Column F... that will get you the dersired result in all other tabs (Axis Rewads and Axis Milestone).

 

[mgforce]

Was busy last week. I will try to do this this coming weekend.
The plan is to do it for all supported scripts.

No issues. Look forward to it

 

[visvin]

@ChurningSidd
This was working like clockwork till 10 days ago. I know the script is running as the emails get labelled as 'axis processed' but the details do not get populated into the Axis tracker file.
I tried to deploy the script again, but didn't help. Any advice on how to fix this?

 

[ChurningNoob]

@ChurningSidd
This was working like clockwork till 10 days ago. I know the script is running as the emails get labelled as 'axis processed' but the details do not get populated into the Axis tracker file.
I tried to deploy the script again, but didn't help. Any advice on how to fix this?

The regex changed did you update it?

 

[visvin]

The regex changed did you update it?

What is regex change? And how do I update it?

 

[shameempkahammed]

am getting this error. can anyone help.

 

[mgforce]

Help needed - I am using IDFC Script of hks789

Script running well and if I run the scrip, I see it reads 2 messages. But when deploying web-app, no new messages being captured..

See 2 emails read..

But nothing here..

What can be going wrong here?

 

[hks789]

Help needed - I am using IDFC Script of hks789

Script running well and if I run the scrip, I see it reads 2 messages. But when deploying web-app, no new messages being captured..

See 2 emails read..

But nothing here..

What can be going wrong here?

Are you able to see the data in the actual google sheets?