Mark-to-Market for a Portfolio of Stocks

Import Libraries

import pandas as pd
import time
import requests
import json 

Define Functions

The functions below are written to query the API more effectively.

Per best practice, the API key is stored in a text file within the programming environment, to prevent revealing the key in the codes. The file has one single line shown below. The X’s is a dummy for the actual API key which can be obtained for free from Alphavantage.

alphavantage: XXXXXXXXXXXXXXXX

def getKeys(keyFile):
    f = open(keyFile)
    lines = f.readlines()
    f.close()
    keys = {}
    for l in lines:
        k,v = l[:-1].split(':')
        keys[k] = v
    return keys

def getParamString(params):
    paramString = ''
    for k, v  in params.items():
        if paramString == '':
            paramString = k + '=' + v
        else:
            paramString = paramString + '&' + k + '=' + v
    return paramString

def tryGET(maxTries, reqURL, progress = True, sleep = 5):
    reqStatus = -1
    returnedStatus = ''
    data = {}
    tries = 0
    
    while tries < maxTries: # monitor number of successful requests made. 
        try:
            req = requests.get(reqURL)
        except requests.exceptions.RequestException as e:
            print(e)
            time.sleep(10)   
            continue # try indefinitely every 10 seconds until request is successful.
            
        if progress:
            print('Try: %i. ' %(tries+1))
        
        time.sleep(sleep)
        tries +=1
        reqStatus = req.status_code
        if reqStatus != 200:  
            continue # try again if the successful request's returned status is not 200.
        else: 
            data = json.loads(req.text)
            if 'Error Message' in data:
                returnedStatus = data['Error Message']
            else:
                returnedStatus = 'OK'
                
            if returnedStatus != 'OK': 
                continue # try again if API returned error.
            else:
                break
                
    return reqStatus, returnedStatus, data, tries

User Inputs

User should review the variables here and change accordingly.

query_date = '2018-03-23'
my_currency = 'SGD'
key_file ='API_key.txt'
portfolio_file = 'my_portfolio.csv'

Read files

An input file that contains the details of the portfolio will be read. The file will have 3 columns: Symbol, Number of shares and Currency, as shown below

keys = getKeys(key_file)
pf = pd.read_csv(portfolio_file)
pf

Query API

currencies = pf['Currency'].unique().tolist()
url = 'https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&apikey='+keys['alphavantage']
rates = {my_currency:1}

for c in currencies:
    if c == my_currency:
        continue
    print('Getting exchange rates for %s' %c) 
    params = {'from_currency': c,
              'to_currency': my_currency}
    paramStrings = getParamString(params)
    reqStatus, returnedStatus, data, tries = tryGET(3, url+'&'+ paramStrings , progress = True, sleep = 5)
        
    if 'Realtime Currency Exchange Rate' not in data:
        xchange_rate = 'NA'
    elif '5. Exchange Rate' not in data['Realtime Currency Exchange Rate']:
        xchange_rate = 'NA'
    else:
        xchange_rate = float(data['Realtime Currency Exchange Rate']['5. Exchange Rate'])  
    
    rates[c] = xchange_rate

url = 'https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&apikey='+keys['alphavantage']
stock_price = {}

for i in range(pf.shape[0]):
    sym = pf.loc[i,'Symbol']
    print('Getting closing price for %s' %sym)
    
    params = {'symbol': sym}
    paramStrings = getParamString(params)
    reqStatus, returnedStatus, data, tries = tryGET(3, url+'&'+ paramStrings , progress = True, sleep = 5)
    
    if 'Time Series (Daily)' not in data:
        close_price = 'NA'
    elif query_date not in data['Time Series (Daily)']:
        close_price = 'NA'
    elif '4. close' not in data['Time Series (Daily)'][query_date]:
        close_price = 'NA'
    else:
        close_price = float(data['Time Series (Daily)'][query_date]['4. close'])        
    
    stock_price[sym] = close_price    

pf = pd.read_csv(portfolio_file)
pf = pf.join(pd.Series([rates[c] for c in pf['Currency']], name = 'Exchange rate'))
pf.set_index('Symbol', inplace = True)
pf['Close price'] = pd.DataFrame.from_dict(stock_price, orient = 'Index')
pf['Value'] = round(pf['Number of shares']*pf['Exchange rate']*pf['Close price'],2)
print('Portfolio market value is %s %.2f' %(my_currency,round(pf['Value'].sum(),2)))
pf