API

Alpha vantage has 5 req/min, 500 req/day recommendation, they probably throttle after that. I'm thinking we start with tracking 100 top stocks. We update our DB at close, using some sort of timing function to make rolling requests for the 100 stocks over 20 minutes after close. This way we have plenty of spare room for API calls to get other data like moving averages, etc. for analysis. As stretch, we could add in a midday update or something, I think tracking the close is a good place to start though, and adding in more requests later should be a relatively easy throw-in.

For the top 100 stocks, I think we need to think of criteria and keep them static going forward. Dynamically choosing a top 100 would potentially stop tracking stocks that exist in a portfolio. Robinhood might be a good reference to use, since they are the stocks people seem to like to play around with.

For getting data into the DB, I think we could go with the intraday api call: https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=MSFT&interval=5min&apikey=demo. This should give us the day's trading info for the given stock symbol, which we can put into DB. We can specify our granularity as we like. Example response:

{
    "Meta Data": {
        "1. Information": "Intraday (5min) open, high, low, close prices and volume",
        "2. Symbol": "MSFT",
        "3. Last Refreshed": "2020-03-12 16:00:00",
        "4. Interval": "5min",
        "5. Output Size": "Compact",
        "6. Time Zone": "US/Eastern"
    },

    "Time Series (5min)": {
        "2020-03-12 16:00:00": {
            "1. open": "142.1200",
            "2. high": "142.1800",
            "3. low": "139.8800",
            "4. close": "139.8800",
            "5. volume": "3253227"
        },
        "2020-03-12 15:55:00": {
            "1. open": "143.2000",
            "2. high": "145.1600",
            "3. low": "142.0800",
            "4. close": "142.1600",
            "5. volume": "2255620"
        },...

Note that this defaults to compact (most recent 100) but that covers a wall-street day.

For seeding history, we can use: https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=MSFT&outputsize=full&apikey=demo . This should give us full history backwards in time for each stock. Note we can choose whether we want daily, weekly, monthly etc. Example response:

{
    "Meta Data": {
        "1. Information": "Daily Time Series with Splits and Dividend Events",
        "2. Symbol": "MSFT",
        "3. Last Refreshed": "2020-03-12 15:53:58",
        "4. Output Size": "Full size",
        "5. Time Zone": "US/Eastern"
    },

    "Time Series (Daily)": {
        "2020-03-12": {
            "1. open": "145.3000",
            "2. high": "153.4700",
            "3. low": "141.0500",
            "4. close": "143.9200",
            "5. adjusted close": "143.9200",
            "6. volume": "70156691",
            "7. dividend amount": "0.0000",
            "8. split coefficient": "1.0000"
        },
        "2020-03-11": {
            "1. open": "157.1304",
            "2. high": "157.7000",
            "3. low": "151.1500",
            "4. close": "153.6300",
            "5. adjusted close": "153.6300",
            "6. volume": "56029209",
            "7. dividend amount": "0.0000",
            "8. split coefficient": "1.0000"
        }, .... 

I'm imagining we run a one time seed, then update stocks daily going forward. We can also break those queries up nicely into tasks, the original seed and rolling query functions, since they don't depend on the rest of the code.