Alphavantage / GetYahoo API and Calc import

[Dunkgrease]

Has anyone used the Alphavantage API to get real time stock prices into LibreOffice Calc (or is there another alternative since the demise of GetYahoo.

If so can someone give me some pointers as to how to use it?

Thanks

[avei]

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

[Ratslinger]

Hello,

Have no real experience with this API. However data can be gotten in JSON or CSV format. A quick test using their samples shows for CSV:

which is a simple Text Import into Calc.

If you are looking for more automation (for example updates every x minutes) you will probably need to use the JSON format:

{
    "Meta Data": {
        "1. Information": "Batch Stock Market Quotes",
        "2. Notes": "IEX Real-Time Price provided for free by IEX (https://iextrading.com/developer/).",
        "3. Time Zone": "US/Eastern"
    },
    "Stock Quotes": [
        {
            "1. symbol": "MSFT",
            "2. price": "100.1500",
            "3. volume": "65641398",
            "4. timestamp": "2018-06-15 16:43:56"
        },
        {
            "1. symbol": "FB",
            "2. price": "195.8100",
            "3. volume": "21828955",
            "4. timestamp": "2018-06-15 16:30:08"
        },
        {
            "1. symbol": "AAPL",
            "2. price": "189.0700",
            "3. volume": "61564949",
            "4. timestamp": "2018-06-15 16:33:26"
        }
    ]
}

Unfortunately in order to use this you will need to write code (python most likely) specific to your needs which will extract the data, move it to a spreadsheet and update it at intervals. The product itself is documented with samples. The interface to LO depends upon your approach - simple (CSV) or complex with added features (JSON).