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.

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
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)