[Axis] Automatic credit card transaction and rewards tracker

ChurningNoob · Dec 14, 2022

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 –

Visit script.google.com in browser where only one google account is logged in
Click on Create app scripts
Replace Code.gs content with https://gist.github.com/TheSidd/d439779aa048ebcbe9a51ff0514c5751#file-code-gs
Paste your copied excel URL in line 62
Click Save
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
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
Click Save icon

Now your script should look like this

Click on Deploy – New Deployment
Now on the pop up click on Setting – select Web App – Provide description and click Deploy
Now Authorize access to script, you will receive warning
Click on Advance – then Click Go to Project-name (unsafe)
Click on allow
A URL should be generated, open and you should see your transactions.
Click on Save data to Sheet
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.

Back to the script page
On left panel click on Triggers (Clock icon)
Add trigger
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?

vigneshelan · Jun 13, 2023

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

Sure, I like this idea. Can you please send me a DM? I don't see any option to message you.

vigneshelan · Jun 13, 2023

hks789 said:
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)

Sure, I will add it in the next release. Thanks for your feedback.

mgforce · Jun 13, 2023

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

Ok. I changed Google Mail settings to disable conversation and still same issue. 9 mails read when I run the code but only 1 parsed.

I also observed that rest 8 transactions are not in INR but in TRY

The one transaction being read is of INR; below

Transaction Successful! INR 2.00 spent on your IDFC FIRST Bank Credit Card ending XX4663 at ONE MOBIKWIK SYSTEM on 02-JUN-2023

The others are all in TRY and not read

Hope you had a great meal! TRY 213.00 spent on your IDFC FIRST Bank Credit Card ending XX4663 at SBX IST NEW AIRPRT INTE on 10-JUN-2023 at 07:19 PM

Is there some linkage to INR etc @hks789 ?

hks789 · Jun 13, 2023

Y

mgforce said:
Ok. I changed Google Mail settings to disable conversation and still same issue. 9 mails read when I run the code but only 1 parsed.

I also observed that rest 8 transactions are not in INR but in TRY

The one transaction being read is of INR; below

The others are all in TRY and not read

Is there some linkage to INR etc @hks789 ?

Yep my RegEx is not designed to handle other currencies.

mgforce · Jun 13, 2023

hks789 said:
Y

Yep my RegEx is not designed to handle other currencies.

Ok, I edited REGEX to TRY temporarily and parsed all info. Changed it back to INR now 🙂

hks789 · Jun 13, 2023

mgforce said:
Ok, I edited REGEX to TRY temporarily and parsed all info. Changed it back to INR now 🙂

You also need to convert LIRA to INR for proper calculations.
That's why I left it out for now. Will see if I can do something to convert it to INR in script itself. Will not be accurate as the exchange rate changes.

mgforce · Jun 14, 2023

hks789 said:
You also need to convert LIRA to INR for proper calculations.
That's why I left it out for now. Will see if I can do something to convert it to INR in script itself. Will not be accurate as the exchange rate changes.

Yes. Here I wanted to track broad spends as I need to claim travel.expenses and don't wanna miss transactions.

Also wanted to track this for ongoing 10X spends based offer from IDFC

ChurningNoob · Jun 15, 2023

Script has been updated should work now.

GitHub - TheSidd/EmailExpenseTracker

Contribute to TheSidd/EmailExpenseTracker development by creating an account on GitHub.

github.com

foden · Jun 15, 2023

S

ChurningSidd said:
Script has been updated should work now.

GitHub - TheSidd/EmailExpenseTracker

Contribute to TheSidd/EmailExpenseTracker development by creating an account on GitHub.

github.com

Still not working for me

kej · Jun 15, 2023

hks789 said:
You also need to convert LIRA to INR for proper calculations.
That's why I left it out for now. Will see if I can do something to convert it to INR in script itself. Will not be accurate as the exchange rate changes.

What I did to solve this problem is that I edited the script to add another column in excel which has the currency information. Then using the google finance function I converted the currency value to the current INR value in the excel sheet itself. Now I have all the international transactions being automatically converted to INR.
It would have been possible to do the convert in the script itself but I wanted the visibility of the original spend currency in my excel sheet so I used the sheets to convert.

hks789 · Jun 15, 2023

kej said:
What I did to solve this problem is that I edited the script to add another column in excel which has the currency information. Then using the google finance function I converted the currency value to the current INR value in the excel sheet itself. Now I have all the international transactions being automatically converted to INR.
It would have been possible to do the convert in the script itself but I wanted the visibility of the original spend currency in my excel sheet so I used the sheets to convert.

The thing with excel is that, it will dynamically convert it based on the day it is opened right?

Script one can make the conversion based on the day it is parsed.

One approach that may work is to copy the base currency to sheet and do conversion in script

vishnum26 · Jun 15, 2023

@ChurningSidd
Raised a new Pull request: https://github.com/TheSidd/EmailExpenseTracker/pull/1.
Please approve. The pr is more accurate.

ChurningNoob · Jun 16, 2023

kej said:
What I did to solve this problem is that I edited the script to add another column in excel which has the currency information. Then using the google finance function I converted the currency value to the current INR value in the excel sheet itself. Now I have all the international transactions being automatically converted to INR.
It would have been possible to do the convert in the script itself but I wanted the visibility of the original spend currency in my excel sheet so I used the sheets to convert.

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

kej · Jun 16, 2023

hks789 said:
The thing with excel is that, it will dynamically convert it based on the day it is opened right?

Script one can make the conversion based on the day it is parsed.

One approach that may work is to copy the base currency to sheet and do conversion in script

I think the sheet will convert it when there is an entry and not when I open it... so I'm assuming it should be picking the rate when the script runs.

kej · Jun 16, 2023

ChurningSidd said:
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

I am still using your original code as of now, maybe you added this currency conversion in your more recent revision? Because in the older version of your code the currency was not being captured in excel so I had to made some changes to your script.

ChurningNoob · Jun 16, 2023

kej said:
I am still using your original code as of now, maybe you added this currency conversion in your more recent revision? Because in the older version of your code the currency was not being captured in excel so I had to made some changes to your script.

Yes, I have updated script but currency conversion formula is in excel. Script is updated to capture the currency from email

mgforce · Jun 16, 2023

kej said:
What I did to solve this problem is that I edited the script to add another column in excel which has the currency information. Then using the google finance function I converted the currency value to the current INR value in the excel sheet itself. Now I have all the international transactions being automatically converted to INR.
It would have been possible to do the convert in the script itself but I wanted the visibility of the original spend currency in my excel sheet so I used the sheets to convert.

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?

kej · Jun 16, 2023

mgforce said:
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?

My script is only for Magnus... it's actually not much different that Sidd's original script. I have just added the ability to capture the currency from the email to excel sheet ( Which, as per the last comment, Sidd has done in his script as well).

kej · Jun 16, 2023

ChurningSidd said:
Yes, I have updated script but currency conversion formula is in excel. Script is updated to capture the currency from email

Did you also implement the "remove duplicate" feature in your script? It helps avoid some edge cases of duplicate entries.

kej said:

Add this function to your script:

Code:

/**
 * 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.

ChurningNoob · Jun 16, 2023

kej said:
Did you also implement the "remove duplicate" feature in your script? It helps avoid some edge cases of duplicate entries.

Awesome! I did not know about this. Please create a PR

[Axis] Automatic credit card transaction and rewards tracker

TF Ace

Attachments

TF Buzz

TF Buzz

TF Ace

TF Ace

TF Ace

TF Ace

TF Ace

TF Ace

TF Buzz

TF Select

TF Ace

TF Neo

TF Ace

TF Select

TF Select

TF Ace

TF Ace

TF Select

TF Select

TF Ace

Similar threads