Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Python script to fetch stock prices for gnucash using http://alphavantage.co/ api. Since Quotes::Finance fetching from Yahoo finance no longer works, it is an alternative.
# 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
import pandas as pd
import sqlite3
import sqlalchemy
from sqlalchemy import create_engine
import datetime
import uuid
import requests
import json
import logging
logger = logging.getLogger(__name__)
# PUT YOUR GNUCASH sqlite3 DATABASE FILENAME HERE. THIS SCRIPT SHOULD BE IN THE SAME FOLDER.
file_name='itau.gnucash'
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)
com = pd.read_sql_table('commodities', engine)
# 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'))
apikey='YOUR_ALPHAVANTAGE_API'
function='TIME_SERIES_DAILY'
rooturl = 'https://www.alphavantage.co/query?'
# 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)
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)'''
res=df[df['mnemonic']==tic]
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']
tickguid = get_ticker_guid(b['Meta Data']['2. Symbol'], com)
prices_dict_to_append['type'] = 'close'
prices_dict_to_append['commodity_guid'] = tickguid
last_price = b['Time Series (Daily)'][last_refreshed]['4. close']
prices_dict_to_append['value_num'] = int(float(b['Time Series (Daily)'][last_refreshed]['4. close'])*10**4)
prices_dict_to_append['value_denom'] = 10**4
date = last_refreshed.replace('-','')+'180800'
prices_dict_to_append['date'] = date
# print(b['Meta Data'])
# print(prices_dict_to_append)
return b['Meta Data'], 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)
# APPEND NEW PRICES TO GNUCASH DATABASE
new_prices.to_sql('prices', engine, if_exists="append", index=False)
@deanbatten

This comment has been minimized.

Copy link

deanbatten commented Mar 8, 2018

Thank you for this. It seems to work nicely. But I wonder if lock checking can be added.

I ran a test: while I had the file open, I ran the update script. Then I closed the file and opened it again, and the file seems corrupted.

Can the script check to see if the file is already open and abort if so? I just converted from xml to sql3 so I could use this script, and I no longer see the .LCK files I used to see when the xml file was open. So, I guess I'd better ask those who know better.

@rgreen13

This comment has been minimized.

Copy link

rgreen13 commented Sep 3, 2018

Awesome script! I created an updated version at https://gist.github.com/rgreen13/643449c327e66cf749a519490a689361 to handle the AlphaVantage API Limit

@thomasjimmy

This comment has been minimized.

Copy link

thomasjimmy commented Mar 24, 2019

Thank you for the Script. As Alphavantage does not work or Indian Stock Exchanges, I created a fork which uses another API here. https://gist.github.com/thomasjimmy/3234ee5377693ae926f7e42c8e0038ea

This works for stocks in India.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.