Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

I have some python code to replace the SMF macros that used yahoo. The code is in its early "let's see what we can do" phase, and so far the results are ok, but I'm having a problem with alpha-vantage.com: namely, it is now rate limited to 1 transaction every 15 seconds or so (4 tran/min). My code is python-flask, and the trial implementation is simply that a trial, but with a little work it may work well with libreoffice Calc. In other applications, I've written, I have used ezodf libraries to access and update libreoffice calc spreadsheets. I'm pretty sure this code can be built into a macro, but I haven't done that yet (running on OSX and OSX doesn't play all that nice with python3).

class smf:
     ticker = ' '
     price  = 0.00
     time_stamp = ' '
     status_code = ' '
     reason_code = ' '


get_alpha(ticker):

# instantiate an smf variable

result = smf()


ts = app.config['_APIKEY']
av_url = 'https://www.alphavantage.co/query'

# First check for current market prices -- works for stocks

function = 'TIME_SERIES_INTRADAY'
interval = '60min'

data = {    "function": function,
            "symbol" : ticker.upper(),
            "interval" : interval,
            "apikey" : ts,
        }

page = requests.get(av_url, params=data)
#pprint.pprint(page.json())
msg = page.json()

if msg.get('Meta Data'):
    # we have a winner -- valid data!
    #pprint.pprint(msg)
    key = "Time Series (60min)"
else:
    #print("Bad Call return code is ", page.status_code)
    # We probably have a mutual fund...go to Daily function
    function = 'TIME_SERIES_DAILY'
    data = {    "function" : function,
            "symbol" : ticker.upper(),
            "apikey" : ts }

    page = requests.get(av_url, params=data)
    msg = page.json()

    if msg.get('Meta Data'):
        key = "Time Series (Daily)"
    else:
        print("Badness has happened and we are out of here! ",page.status_code, msg )
        result.status_code = '505'
        result.reason_code = msg
        return result


#  At this point, we have a valid message containing ticker data.  It is either in Stock Format or
#  Daily Fund format.
#
#  Now we need to get the most current price for the whatever (stock,fund, etf, ?bond)
#  Fortunately this appears to be easy.  The 'Meta Data' element in both cases contains
#   '2. Symbol' : and the ticker
#   '3. Last Refreshed' : and either the date or the time stamp depending on whether we are stock or fund, but...
#                         it doesn't matter, since that's the key and its consistent for either.
#  Once we have the 'Last Refreshed' element safely extracted, we can use that to go get the data:
#  'Time Series (Daily)'  or 'Time Series (60min)' depending on which one we got.  We may have to set up a selector
#  to get to the right tag, but that should be fairly simple.  Then take the timestamp or date from the Last Refreshed
#  and get the '4. close' element which should be the price.
#

updated_at = msg['Meta Data']['3. Last Refreshed']

# now we need to get the second level key in a variable
tick_value  = msg[key][updated_at]['4. close']

print("Value is ", msg['Meta Data']['2. Symbol'], tick_value, "@", updated_at)
result.ticker = msg['Meta Data']['2. Symbol']
result.price = tick_value
result.time_stamp = updated_at

return result