Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

    Why not have the Excel spreadsheet linked directly to Access as a separate Table. Then in a query you can join it to what ever other data you have. The excel file does not need to be open for this type of set up.

    https://support.microsoft.com/en-us/...d-e084913cc958

  2. #17
    samsquared is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2021
    Location
    St. Augustine, FL
    Posts
    20
    I can't get the Michael Saunders method to work presumably because I'm using Excel 2010 (it says 2013 and above). So, I went to the Google sheet to add some stocks. I see them in the Excel spreadsheet, but my linked Access table now just has #ERROR in each cell. I tried to re-link the spreadsheet but am getting a message saying "the connection for viewing your linked microsoft excel worksheet was lost" and so far I've been unable to fix that.

  3. #18
    samsquared is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2021
    Location
    St. Augustine, FL
    Posts
    20
    I figured out how to do this directly from MS Access without using Google sheets or Excel. Finnhub.io lets you get a free api key so you can access a portion of their content. Fortunately, it includes access to stock quotes.


    You'll need to create an MSXML2.XMLHTTP object, do the send, and parse the result string. The format of the call I do is like this one for Exxon:


    Code:
    "https://finnhub.io/api/v1/quote?symbol=XOM&token=MyAPITokenGoesHere&rnd=615903378"

    Note the extra parm on the end. I discovered that re-sending the request yields the same return value because it's cached. There are trickier ways to address this but the easiest is to add an extra parm (I generate a random number each time - using 'rnd' was just my choice) thus making the call unique. It works!


    Finnhub limits free accounts to 60 calls/minute which isn't a problem for me. For dividend info I use Polygon.io which restricts free account calls to 5 calls/minute which is somewhat inconvenient, but dividend information doesn't change very often for the stocks I track.


    So, for free:
    Finnhub gets you quotes and access to basic stock info like 52-week highs and lows and quotes, but no dividends.
    Polygon gets you dividend info but no quotes.


    Hope this helps. I can post some sample code if anyone wants.

  4. #19
    writeprivate is offline Advanced Access Coder
    Windows 10 Access 2021
    Join Date
    Feb 2023
    Posts
    42
    Quote Originally Posted by samsquared View Post
    I figured out how to do this directly from MS Access without using Google sheets or Excel. Finnhub.io lets you get a free api key so you can access a portion of their content. Fortunately, it includes access to stock quotes.


    You'll need to create an MSXML2.XMLHTTP object, do the send, and parse the result string. The format of the call I do is like this one for Exxon:


    Code:
    "https://finnhub.io/api/v1/quote?symbol=XOM&token=MyAPITokenGoesHere&rnd=615903378"

    Note the extra parm on the end. I discovered that re-sending the request yields the same return value because it's cached. There are trickier ways to address this but the easiest is to add an extra parm (I generate a random number each time - using 'rnd' was just my choice) thus making the call unique. It works!


    Finnhub limits free accounts to 60 calls/minute which isn't a problem for me. For dividend info I use Polygon.io which restricts free account calls to 5 calls/minute which is somewhat inconvenient, but dividend information doesn't change very often for the stocks I track.


    So, for free:
    Finnhub gets you quotes and access to basic stock info like 52-week highs and lows and quotes, but no dividends.
    Polygon gets you dividend info but no quotes.


    Hope this helps. I can post some sample code if anyone wants.

    Hi Sam

    I have a similar requirement. Looking to do this directly in Access with api rather than linking to Sheets/Excel. Wondered how you are getting along with your solution now?

    Please could you post your sample code - that would be helpful. I haven't yet called an api in Access so would love to know the various steps I need to take. It would be great to see how you have approached this.

    Also, did you find any other services to give you the data you need? One issue i have is that lots of the ETFs and Funds i need to track don't have "tickers" but use the international standard ISINs as the unique reference (like a barcode is universal) - not sure why GoogleFinance and Excel use tickers.

    Thanks in advance

  5. #20
    samsquared is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2021
    Location
    St. Augustine, FL
    Posts
    20
    This is the base code for using an API to get information from Finnhub and Polygon. Calls are made to these subs from other subs which iterate through a stock table to provide the stock symbol. I've removed some enhancements for tasks not related to the API calls. I'm not an expert VBA coder so the code below may not be the best way to do it but it does work.

    Finnhub is used for stock prices; variations can be used to retrieve 52-week high and low and stock name:


    Code:
    Public Sub GetQuotesFinnhub(strSymbol, numID, StockQuote, _
        Optional ByVal boolAPICallsExceeded As Boolean, _
        Optional lngBatch As Long)
    
    
    Dim objXML As Object
    Dim strTemp, strURL, strCurrent, strResponse, strSQL As String
    Dim curDividend As Currency
    Dim dblCurrentPrice As Double
    Dim arrResponse, arrCurrent As Variant
    Dim intRandom As Long
    Dim intStockOwned, intResponse As Integer
    
    
    ' My code to set a random number to add to the call
    GetRandom intRandom
    
    
    Set objXML = CreateObject("MSXML2.XMLHTTP")
    'conFinnhubAPIKey is API key provided by Finnhub when signing up for an account
    strURL = "https://finnhub.io/api/v1/quote?symbol=" & strSymbol & "&token=" & conFinnhubAPIKey _
        & "&rnd=" & intRandom
    
    
    objXML.Open "GET", strURL
    objXML.Send
    
    
    Do
        DoEvents
    Loop Until objXML.ReadyState = 4
    
    
    strResponse = objXML.ResponseText
    
    
    If InStr(strResponse, "API limit reached") > 0 Then
    
    
        boolAPICallsExceeded = True
        Set objXML = Nothing
        Exit Sub
    End If
    
    
    If InStr(strResponse, "Bad gateway") > 0 Then
        MsgBox (strResponse)
        Exit Sub
    End If
    
    
    arrResponse = Split(objXML.ResponseText, ",")
    strCurrent = arrResponse(0) '"{"status":"OK"
    arrCurrent = Split(strCurrent, ":")
    
    
    strTemp = Mid(arrCurrent(1), 1, Len(arrCurrent(1)))
    
    
    dblCurrentPrice = CDbl(strTemp)
    
    
    strSQL = "insert into FinnHubPrices (Symbol, Quote, QuoteDate, StockOwned, Batch )" _
         & " VALUES ('" & strSymbol & "',  " & dblCurrentPrice & ", Now(), " & intStockOwned & ", " & lngBatch & " )"
    
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    
    
    DoCmd.SetWarnings True
    
    
    Set objXML = Nothing
    
    
    End Sub
    Polygon is used for dividends:

    Code:
    Public Sub GetQuotesPolygon(strSymbol, numID, strDisplay, dblAnnualDividend, _
        Optional dtExDividend As Date, _
        Optional dtPayDate As Date, _
        Optional dblDividend As Double, _
        Optional intFrequency As Integer)
    
    
    Dim objXML As Object
    Dim strTemp, strURL, strStatus, strResponse, strSQL, strSeparator   As String
    Dim curDividend As Currency
    Dim arrResponse, arrTempArray As Variant
    Dim intRandom As Long
    Dim ResponseItem As Variant
    Dim intReturnCount, intCount As Integer
    
    
    Dim objFS, objDiv As Object
    
    
    Set objXML = CreateObject("MSXML2.XMLHTTP")
    
    
    CallPolygon:
    
    
    ' My code to set a random number to add to the call
    GetRandom intRandom
    
    
    'conPolygonAPIKey is API key provided by Polygon when signing up for an account
    strURL = "https://api.polygon.io/v3/reference/dividends?ticker=" & strSymbol _
        & "&dividend_type=CD&limit=1&apiKey=" & conPolygonAPIKey & "&rnd=" & intRandom
    
    
    objXML.Open "GET", strURL
    objXML.Send
    
    
    Do
        DoEvents
    Loop Until objXML.ReadyState = 4
    strResponse = objXML.ResponseText
    
    
    ' Debug watch window can't always show wntire response so I write it out to text file
    Set objFS = CreateObject("Scripting.FileSystemObject")
    Set objDiv = objFS.OpenTextFile("c:\#Temp\PolygonResponse.txt", 8, True)
    
    
    strSeparator = "------------------------------- " & Now() & " ---------------------------------------"
    objDiv.WriteLine (strSeparator)
    objDiv.WriteLine (strResponse)
    objDiv.Close
    Set objFS = Nothing
    Set objDiv = Nothing
    
    
    arrResponse = Split(objXML.ResponseText, ",")
    strStatus = arrResponse(1) '"{"status":"OK"
    If InStr(strStatus, "status") And InStr(strStatus, "OK") Then
        strDisplay = "*** No dividend information available ***"
        Set objXML = Nothing
        Exit Sub
    End If
    
    
    intReturnCount = 0
    
    
    For Each ResponseItem In arrResponse
        If InStr(ResponseItem, "cash_amount") Then
        arrTempArray = Split(ResponseItem, ":")
        strTemp = arrTempArray(2) '<==== Item 3 (array element 2) because it's the first result
        dblDividend = CDbl(arrTempArray(2))
        intReturnCount = intReturnCount + 1
    
    
        ElseIf InStr(ResponseItem, "ex_dividend_date") Then
        arrTempArray = Split(ResponseItem, ":")
        strTemp = Mid(arrTempArray(1), 2, 10)
        dtExDividend = DateValue(strTemp)
        intReturnCount = intReturnCount + 1
    
    
        ElseIf InStr(ResponseItem, "frequency") Then
        arrTempArray = Split(ResponseItem, ":")
        strTemp = arrTempArray(0)
        strTemp = arrTempArray(1)
        intFrequency = strTemp
        intReturnCount = intReturnCount + 1
    
    
        ElseIf InStr(ResponseItem, "pay_date") Then
        arrTempArray = Split(ResponseItem, ":")
        strTemp = Mid(arrTempArray(1), 2, 10)
        dtPayDate = DateValue(strTemp)
        intReturnCount = intReturnCount + 1
    
    
        End If
    
    
    If intReturnCount = 4 Then Exit For
    
    
    Next
    
    
    If intReturnCount < 4 Then
        MsgBox ("Not all return values received from Polygon; exiting.")
        Exit Sub
    End If
    
    
    'Insert info into table if it doesn't already exist
    intCount = DCount("*", "tblDividendsFuture", "StockID = " & numID & " and [Next Paid] = #" & dtPayDate & "#")
    If intCount = 0 Then
        strSQL = "insert into tblDividendsFuture (StockID, [Next Paid], [Next Ex-Dividend], Dividend) " & _
            "Values ('" & numID & "', #" & Format(dtPayDate, "mm/dd/yyyy") & "# , " & _
            " #" & Format(dtExDividend, "mm/dd/yyyy") & "#, " & dblDividend & ")"
        DoCmd.RunSQL strSQL
    End If
    
    
    DoCmd.SetWarnings True
    
    
    Set objXML = Nothing
    
    
    End Sub
    I hope this helps. I assume the forum has a method to message me directly so please feel free to do so.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 04-02-2019, 06:45 AM
  2. Market Data Retrieval
    By Grefcon901 in forum Access
    Replies: 16
    Last Post: 04-07-2016, 01:59 PM
  3. Replies: 22
    Last Post: 12-20-2015, 02:46 AM
  4. Replies: 1
    Last Post: 07-11-2012, 01:42 PM
  5. Calculating Current Stock in Inventory/Warehouse
    By yohansetiawan in forum Access
    Replies: 5
    Last Post: 03-14-2012, 09:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums