Gmail Script for MAS 10-Year SORA (Singapore Overnight Rate Average) Chart
I had a friend who was working in the loans department. They had to use the MAS 10-Year Domestic Interest Rates chart on a daily basis. The values could be obtained from this website.
We have two choices. To download, or to display. If we download, we can get a csv file as below.
If we display, we can get a nice UI showing the rates on the website.
My friend would take the values, and create a chart on excel, resize it, then save it on her iPad for use during presentations. It was a small chore but had to be done daily. She spent around 5–10 mins daily doing this.
I proposed to write her a python script that would automate that process. The script would send her an email with the graph she wanted. The government website had an API that I could connect to, to extract the info. The API information is available here.
My plan was to:
- Extract the info from the API
- Create a chart that shows at least 10 years worth of data.
- Save the information to a image file.
- Send out the image file thru GMail SMTP port (SSL)
Once I had the script done, I would be able to automate it by running an instance on cloud and setting up a CRON to do it on a daily basis. We can even do it for free for one year if we are using the AWS free tier. We could also use AWS Lambda to run it but it would be a bigger hassle as we have to install dependencies etc separately depending on the Lambda settings.
1. Extracting Info from the API
We will start with importing all the relevant libraries.
import urllib.request
import json
from datetime import date, timedelta # timedelta not necessarily used.
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import os # Not necessarily used if not saving locally.
# Use io to save the figure to memory buff.
import io
# Libraries for the email
import smtplib, ssl
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
from email.mime.image import MIMEImageI will be putting all my code within a function called runfunction().
def runfunction():
'''
Start of code
End of code
'''
return "All items executed."
a = runfunction()
print(a)For the start of the code, I will get today’s date, and go 11 years back (just to make sure I have > 10 years of data). I will also get the info of “comp_sora_1m” and “comp_sora_3m”, which are the compounded SORA rates for 1 month and 3 months. I will then store the information into a list.
enddate = date.today()
numberofyears = 11
for x in range(1,6):
helperstart = (x-1)*2
helperend = x*2
a = f"{enddate.year - numberofyears + helperstart}-01-01"
b = f"{enddate.year - numberofyears + helperend}-01-01"
locals()['daterange'+str(x)] = f'&between[end_of_day]={a},{b}'
startdate6 = f"{enddate.year - numberofyears + 10}-01-02"
enddate6 = date.today()
daterange6 = f'&between[end_of_day]={startdate6},{enddate6}'
fieldtype0 = 'end_of_day'
fieldtype1 = 'comp_sora_1m'
fieldtype2 = 'comp_sora_3m'
totalfield = f"&fields={fieldtype0},{fieldtype1},{fieldtype2}"
headers = {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36'
}
listofurl =[]
for x in range(1,7):
locals()[str("url"+str(x))] = 'https://eservices.mas.gov.sg/api/action/datastore/search.json?resource_id=9a0bf149-308c-4bd2-832d-76c8e6cb47ed&limit=1000'+locals()[str("daterange"+str(x))]+totalfield
listofurl.append(locals()[str("url"+str(x))])
overallcompilation = []
for x in range(1,7):
req = urllib.request.Request(listofurl[x-1], headers=headers)
with urllib.request.urlopen(req) as response:
locals()['data'+str(x)] = json.loads(response.read().decode())
for y in locals()['data'+str(x)]['result']['records']:
overallcompilation.append(y)2. Create a chart that shows at least 10 years worth of data.
Now that we have all the information in the list, we can convert it into a pandas dataframe, and plot the chart using matplotlib.
The below code will rename the column names, and plot the two line on the chart. I have specifically set the figsize to 11.69x8.27, which is the size of an A4 paper. This is to make it convenient for my friend, in case there is a need to print it out and give it to the client.
df1 = pd.DataFrame(overallcompilation)
df1 = df1[['end_of_day','comp_sora_1m','comp_sora_3m']]
df1 = df1.rename(columns={'end_of_day':'date_day','comp_sora_1m':'sora_1m','comp_sora_3m':'sora_3m'})
df1 = df1.dropna(axis=0)
df1['date_day'] = pd.to_datetime(df1['date_day'])
fig, ax = plt.subplots(figsize=(11.69, 8.27))
ax.plot(df1['date_day'], df1['sora_1m'], label='Compounded SORA 1M')
ax.plot(df1['date_day'], df1['sora_3m'], label='Compounded SORA 3M')
ax.set_title('SORA Interest Rates')
ax.set_xlabel('Year - Month (Quarter)')
ax.set_ylabel('Interest Rate (%)')
ax.legend()
quarterly_month_locator = mdates.MonthLocator(interval=3)
ax.xaxis.set_major_locator(quarterly_month_locator)
ax.xaxis.set_minor_locator(mdates.WeekdayLocator(byweekday=mdates.MO))
date_formatter = mdates.DateFormatter('%Y-%m')
ax.xaxis.set_major_formatter(date_formatter)
ax.set_xlim([df1['date_day'].min(), df1['date_day'].max()])
plt.xticks(rotation=90)
ax.text(0, -0.2, f"Additional Remarks: \nStart date: {df1['date_day'].min().strftime('%Y-%m-%d')} End date: {df1['date_day'].max().strftime('%Y-%m-%d')}. Compounded SORA-1M = {df1.iloc[-1]['sora_1m']}%. Compounded SORA-3M = {df1.iloc[-1]['sora_3m']}%.", transform=ax.transAxes, ha='left', va='bottom')At the bottom-most of the chart, I included a text stating the timeframe of this chart, and also provided an actual number on the latest Compounded SORA-1M and SORA-3M rates. This is because the graph is just to show the trend and the number is actually what clients want to know specifically.
As can be seen from the graph, the code from Part 1 had taken slightly more than 10 years, and the date starts from the 1st available date of the year.
3. Save the information to a image / pdf file.
There are two options on saving the image / pdf file. We could save it locally to our computer folder system, or we could save it on memory and email it directly. If the purpose script is just for the user to run it, and get the image plotted out, the first option would be more suitable. If the purpose of the script is to be run on a remote instance and emailed out automatically, the second option would be more suitable.
Option 1 (Saving locally)
try:
# Specify the directory to save the plot in
save_dir = r'C:\\My Drive\\20230924 Scraper for MAS'
# Check if the directory exists
if not os.path.isdir(save_dir):
raise ValueError('Invalid directory specified.')
# Save the plot to the specified directory
plt.savefig(os.path.join(save_dir, f"SORA_plot_{df1['date_day'].max().strftime('%Y-%m-%d')}.pdf"), dpi=600, bbox_inches='tight')
plt.savefig(os.path.join(save_dir, f"SORA_plot_{df1['date_day'].max().strftime('%Y-%m-%d')}.png"), dpi=600, bbox_inches='tight')
print('Plot saved to directory:', save_dir)
except ValueError as e:
# Handle the case where the directory is not valid
print('Error:', e)
print('Saving plot to current directory instead.')
plt.savefig(f"SORA_plot_{df1['date_day'].max().strftime('%Y-%m-%d')}.pdf", dpi=600, bbox_inches='tight')
plt.savefig(f"SORA_plot_{df1['date_day'].max().strftime('%Y-%m-%d')}.png", dpi=600, bbox_inches='tight')
else:
# This block is executed if no exceptions were raised in the try block
plt.show()The code block above tries to find for a pre-determined directory. If it is unable to locate that directory, it will just local the current directory, of where the python script is saved at, and save the charts / plots in that directory.
I had deliberately done it such that the name of the file will include the latest date available of the SORA information. So that the user can just take a look at the file name to know up to what date is the file updated with.
In this instance, the files were updated up to 2023–09–21 (Thursday) as today is a Sunday. On Monday, the 2023–09–22 (Friday) information will be released.
Option 2 (Saving on Memory and Emailed)
pdffilename = f"SORA_plot_{df1['date_day'].max().strftime('%Y-%m-%d')}.pdf"
pngfilename = f"SORA_plot_{df1['date_day'].max().strftime('%Y-%m-%d')}.png"
memory_pdf = io.BytesIO()
plt.savefig(memory_pdf,format='pdf', dpi=600, bbox_inches='tight')
memory_png = io.BytesIO()
plt.savefig(memory_png,format='png', dpi=600, bbox_inches='tight')By using io, we are able to save the files on memory instead of downloading them and then can send out directly thru the email.
4. Send out the image file thru GMail SMTP port (SSL)
We will be using Gmail as it is one of the most popular email service provider.
You will have to replace the sender_email and sender_password with your own email and app password. The app password is not your password but instead another password provided separately by Google. I will talk about this later. You will also have to modify the lstofreceiver to the list of people who you want to send the info to.
Do take note that Gmail has set limits on the amount of messages that can be sent via this method so it is not meant for mass spamming.
port = 465
smtp_server = "smtp.gmail.com"
sender_email = 'youremail@gmail.com'
sender_password = 'yourapppassword'
lstofreceiver = ['receiver1@gmail.com',
'receiver2@gmail.com',
'receiver3@gmail.com']
if len(lstofreceiver) == 1:
receiver_email = lstofreceiver[0]
elif len(lstofreceiver) >= 2:
receiver_email = lstofreceiver
subject = 'MAS 1M 3M Compounded SORA 10 Year Interest Rate Charts'+ f" {df1['date_day'].max().strftime('%Y-%m-%d')}"
body = 'Dear Sir / Madam,\nCharts attached as per attached.'
msg = MIMEMultipart()
msg['Subject'] = subject
msg['From'] = sender_email
if isinstance(receiver_email,str)==True:
msg['To'] = receiver_email
elif isinstance(receiver_email,list)==True and len(receiver_email)>=2:
msg['To'] = ', '.join(receiver_email)
elif isinstance(receiver_email,list)==True and len(receiver_email)==1:
msg['To'] = receiver_email[0]
pdf_output = MIMEApplication(memory_pdf.getvalue(), _subtype='pdf')
pdf_output.add_header('Content-Disposition', 'attachment', filename=pdffilename)
msg.attach(pdf_output)
png_output = MIMEImage(memory_png.getvalue())
png_output.add_header('Content-Disposition', 'attachment', filename=pngfilename)
msg.attach(png_output)
msg.attach(MIMEText(body,'plain'))
print("Before Login")
context = ssl.create_default_context()
with smtplib.SMTP_SSL(smtp_server, port, context=context) as server:
server.login(sender_email, sender_password)
print("After Login")
for x in lstofreceiver:
server.send_message(msg, from_addr=sender_email, to_addrs=x)
server.quit()The code provides a subject, a body, and attaches the pdf file and png file into the email. The result should be as such.
To get the app password, you will have to log in to your Google Account and go into the security tab here.
Click on the 2-Step Verification tab
Go all the way to the bottom and find App Password.
Type in the name of the app or any other name that you are able to recognize to designate this info to. I have written “for_MAS_scraper_demo”
There will be a 16 character password which will be generated as below. The characters will be spaced at every 4 characters. Do remember to key in purely your 16 characters without the space, into the sender_password string.
After your done and you click “Done”, you can see that there is one more additional app password.
Do take note that app passwords are less secure, and there are other more advanced methods such as can be found here. However, they are slightly more complicated and have slightly more requirements. The above method is free and can be done repetitively.
You may try to run your script and have fun.
