Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save alexjj/506e02f3b69a50a674bd1a332797eab4 to your computer and use it in GitHub Desktop.
Save alexjj/506e02f3b69a50a674bd1a332797eab4 to your computer and use it in GitHub Desktop.
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 time
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 ='FILENAME'
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
apikey = 'ALPHAVANTAGE_API_KEY'
function = 'TIME_SERIES_DAILY'
rooturl = 'https://www.alphavantage.co/query?'
def get_ticker_guid(tic, df):
'''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, com):
'''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
if __name__ == "__main__":
engine = create_engine('sqlite:///'+file_name)
# 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'))
# 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 i, ticker in enumerate(tickers):
if (i+1) % 5 == 0:
print("\nSleeping off API Rate Limit...\n")
time.sleep(60)
qstr = 'function={0}&symbol={1}&apikey={2}'.format(function, ticker, apikey)
print("Retrieving ", qstr)
historical[ticker]=requests.get(rooturl+qstr)
# 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, com)
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(new_prices)
# APPEND NEW PRICES TO GNUCASH DATABASE
new_prices.to_sql('prices', engine, if_exists="append", index=False)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment