Python script to fetch stock prices for Stocks in India for GNUCash. Forked from the original as Alphavantage doesn't work for Stocks in Indian Stock Exchange. This uses another free API which is at https://api.mfapi.in/mf/.

fetch_stock_prices_for_gnucash.py

# INSTALL DEPENDENCIES BEFORE RUNNING: pandas, sqlite3, sqlalchemy, requests
# There is one thing you have to do though: gnucash needs to know the price's currency and it needs a guid (unique identifier)
# for this. This guid is unique for each installation, so you need to find yours and assign it to CURRENCY_GUID.
# OR: you can just leave it, and the script will fetch the currency from the last price added

#This creates a backup of your account file with the filename .backup. In case your original file is
#corrupted and GNU cash is unable to open the same, please rename the backup file and use the same.
import pandas as pd
import sqlite3
import sqlalchemy
from sqlalchemy import create_engine
import time

import datetime
import uuid
import requests
import json
import logging
import shutil


logger = logging.getLogger(__name__)

# PUT YOUR GNUCASH sqlite3 DATABASE FILENAME HERE. THIS SCRIPT SHOULD BE IN THE SAME FOLDER.
file_name='MYGnuCashFileName.gnucash'

#Creating a backup of the database file
shutil.copy2(file_name, file_name + '_' + str(datetime.datetime.now())+ '_' +'.backup' ) 
#print 'Creating Backup of the File'
engine = create_engine('sqlite:///'+file_name)


CURRENCY_GUID = None # you can hardcode the currency guid for the stock prices you are fetching. Ex.: '12488f5b3fa63a6e8dd1c7ae6bde3875'

pd.options.display.float_format = '{:,.2f}'.format

pd.options.display.max_columns=200

# GET RELEVANT TABLES
logger.info('READING TABLES')
prices = pd.read_sql_table('prices', engine)
#print prices
com = pd.read_sql_table('commodities', engine)
#print com

# THIS IS JUST TO FACILITATE INSPECTION. YOU MAY DELETE IT IF YOU WISH.
full = prices.merge(com, how='left', left_on=['commodity_guid'], right_on=['guid'], suffixes=('', '_commodity'))
full = full.merge(com, how='left', left_on=['currency_guid'], right_on=['guid'], suffixes=('', '_currency'))

rooturl = 'https://api.mfapi.in/mf/'
# Guess CURRENCY_GUID by last prices fetched
if not CURRENCY_GUID:
    CURRENCY_GUID = prices.iloc[-1]['currency_guid'] 

# GET ALL THE TICKERS IN GNUCASH DB BY MNEMONIC NAME.
tickers = com[com['namespace']!='CURRENCY']['mnemonic']
historical = {}

# FOR EACH TICKER, RETRIEVE INFO ABOUT IT
for ticker in tickers:
    #qstr = 'function={0}&symbol={1}&apikey={2}'.format(function,ticker,apikey)
    qstr = ticker
    historical[ticker]=requests.get(rooturl+qstr)
   
def get_ticker_guid(tic, df=com):
    '''Given a ticket and the commodities table, retrieve guid of ticker
    Return -> ticker_guid (str)'''
    #print 'got the below items'
    #print tic
    #print df
    #res=df[df['mnemonic']==tic]
    #res=df.loc[df['mnemonic']=='118472']
    res=df.loc[df['mnemonic']==str(tic)]
    #print res
    #print 'printing part'
    #print df['mnemonic']
    if res.shape[0]!=1:
        raise Exception('Length error, could not find tickers guid')
    return res.iloc[0]['guid']

def parse_response_to_prices(response):
    '''Should return:
    metadata_dict, prices_dict_to_append'''
    prices_dict_to_append = {}
    prices_dict_to_append['guid'] = uuid.uuid4().hex
    prices_dict_to_append['currency_guid'] = CURRENCY_GUID #HARDCODED
    prices_dict_to_append['source'] = 'PVpyScript'
    
    b=json.loads(response.content.decode())
    #last_refreshed = b['Meta Data']['3. Last Refreshed']
    last_refreshedStr = b['data'][0]['date']
    last_refreshedDt = time.strptime(last_refreshedStr, '%d-%m-%Y')
        
    last_refreshed = time.strftime('%Y-%m-%d', last_refreshedDt)
    #print newstr
    #print 'printing the below items'
    #print b['meta']['scheme_code']
    #print com
    tickguid = get_ticker_guid(b['meta']['scheme_code'], com)

    prices_dict_to_append['type'] = 'close'
    prices_dict_to_append['commodity_guid'] = tickguid

    last_price = b['data'][0]['nav']
    
    prices_dict_to_append['value_num'] = int(float(last_price)*10**5)
    prices_dict_to_append['value_denom'] = 10**5

    date = last_refreshed.replace('-','')+'180800'
    
    prices_dict_to_append['date'] = date
#    print(b['Meta Data'])
#    print(prices_dict_to_append)
    return b['meta'], prices_dict_to_append

# PROCESS EACH TICKER PRICE AND APPEND TO PRICES TABLE
new_prices = prices.iloc[0:0].copy()
for k,v in historical.items():
    print('Processing %s' %k)
    try:
        meta, d = parse_response_to_prices(v)
    except Exception as e:
        print('Could not process this ticker, because of the following exception:')
        print(e)
        #print(v.content)
        continue
    new_prices = new_prices.append(d, ignore_index=True)
    
print 'printing new prices'
print new_prices
    
# APPEND NEW PRICES TO GNUCASH DATABASE
new_prices.to_sql('prices', engine, if_exists="append", index=False)