Reading price of an item in Amazon.
An AI-generated function with AI-generated image.
Not complete, not tested.

To use Amazon's Product Advertising API, you first need to sign up for it. You can do this by signing into your Amazon Associates account, selecting "Tools" from the navigation bar, and then selecting "Product Advertising API" from the dropdown ². After that, you can join by clicking on "Join" ¹. Once you've signed up, you can go to the "Download credentials" page and copy your Secret key or Access key ¹.
Is there anything else you would like to know?
Source: Conversation with Bing, 3/20/2023(1) How to Gain Access to Amazon’s Product Advertising API. Accessed 3/20/2023.
(2) How to use Amazon API: examples and pricing [2023] - Elfsight. Accessed 3/20/2023.
(3) How to use Amazon API: examples and pricing [2023] - Elfsight. Accessed 3/20/2023.
(4) Introduction · Product Advertising API 5.0 - Accessed 3/20/2023.
(5) Amazon Advertising Advanced Tools Center. Accessed 3/20/2023.

Here is an example VBA code snippet that you can use to retrieve the price of a product from Amazon using their Product Advertising API:

What is this?


Not Tested

Sub GetAmazonPrice()
    ' Replace with your Access Key ID and Secret Access Key
    Const ACCESS_KEY_ID As String = "ACCESS_KEY_ID"
    ' Replace with the ASIN of the product you want to look up
    Const ITEM_ID As String = "ASIN"
    Dim objHTTP As Object
    Dim strURL As String
    Dim strTimestamp As String
    Dim strSignature As String
    Dim strResponse As String
    ' Create a timestamp in ISO 8601 format
    strTimestamp = WorksheetFunction.EncodeURL(Format(Now(), "yyyy-mm-dd\THH:mm:ss\Z"))
    ' Build the request URL
    strURL = "" & _
        "Service=AWSECommerceService&" & _
        "AWSAccessKeyId=" & ACCESS_KEY_ID & "&" & _
        "AssociateTag=yourtag-20&" & _
        "Operation=ItemLookup&" & _
        "ItemId=" & ITEM_ID & "&" & _
        "ResponseGroup=Offers&" & _
        "Timestamp=" & strTimestamp
    ' Calculate the signature for the request
    strSignature = CalculateSignature(strURL, SECRET_ACCESS_KEY)
    ' Add the signature to the request URL
    strURL = strURL & "&Signature=" & WorksheetFunction.EncodeURL(strSignature)
    ' Send the HTTP GET request to Amazon's Product Advertising API
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    objHTTP.Open "GET", strURL, False
    ' Get the response text from Amazon's Product Advertising API
    If objHTTP.Status < > 200 Then MsgBox ("Error: Could not connect to Amazon's Product Advertising API")
    If InStr(objHTTP.responseText," <Error >") > 0 Then MsgBox ("Error: Invalid response from Amazon's Product Advertising API")
    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
    xmlDoc.LoadXML (objHTTP.responseText)
    Set xmlNodeListPriceFormattedPrice = xmlDoc.getElementsByTagName("FormattedPrice")
    If xmlNodeListPriceFormattedPrice.Length > 0 Then
        MsgBox ("No price found for this item.")
    End If
End Sub
Private Function CalculateSignature(ByVal strURL As String, ByVal SECRET_ACCESS_KEY As String) As String
    Dim arrParts() as string
    arrParts() = Split(strUrl,"?")
    hostAndPathPartOfUrl = arrParts(0)
    queryPartOfUrlWithoutSignatureValue = arrParts(1)
    queryPartOfUrlWithoutSignatureValueSortedByKeyAsArray() as string
    queryPartOfUrlWithoutSignatureValueSortedByKeyAsArray() = Split(queryPartOfUrlWithoutSignatureValue,"&")
    Call QuickSortArrayOfStrings(queryPartOfUrlWithoutSignatureValueSortedByKeyAsArray(), LBound(queryPartOfUrlWithoutSignatureValueSortedByKeyAsArray()), UBound(queryPartOfUrlWithoutSignatureValueSortedByKeyAsArray()))
    queryPartOfUrlWithoutSignatureValueSortedByKeyAsString as string
    queryPartOfUrlWithoutSignatureValueSortedByKeyAsString = Join(queryPartOfUrlWithoutSignatureValueSortedByKeyAsArray(),"&")
    stringToSign as string
    stringToSign ="GET" + vbNewLine + hostAndPathPartOfUrl + vbNewLine + "/" + vbNewLine + queryPartOfString
End Function
Sub QuickSortArrayOfStrings(ByRef pvarArrayToSort() As Variant, ByVal plngLeftIndex As Long, ByVal plngRightIndex As Long)
    Dim lngFirstIndex            As Long        ' First index when partitioning array.
    Dim lngLastIndex            As Long        ' Last index when partitioning array.
    Dim varMidElement            As Variant        ' Value of middle element used as pivot.
    Dim varSwap                    As Variant        ' Temporary storage used when swapping elements.
    lngFirstIndex = plngLeftIndex: lngLastIndex = plngRightIndex: varMidElement = pvarArrayToSort((plngLeftIndex + plngRightIndex) \ 2)
    Do While (lngFirstIndex <= lngLastIndex)
        ' Find element that is greater than or equal to pivot starting from left side of array.
        Do While (pvarArrayToSort(lngFirstIndex) < varMidElement And lngFirstIndex < plngRightIndex): lngFirstIndex = lngFirstIndex + 1: Loop
        ' Find element that is less than or equal to pivot starting from right side of array.
        Do While (pvarArrayToSort(lngLastIndex) > varMidElement And lngLastIndex > plngLeftIndex): lngLastIndex = lngLastIndex - 1: Loop
        ' Swap elements if necessary.
        If (lngFirstIndex <= lngLastIndex) Then
            varSwap = pvarArrayToSort(lngFirstIndex): pvarArrayToSort(lngFirstIndex) = pvarArrayToSort(lngLastIndex): pvarArrayToSort(lngLastIndex) = varSwap
            lngFirstIndex = lngFirstIndex + 1: lngLastIndex = lngLastIndex - 1
        End If
    ' Recursively call function to sort left and right partitions of array.
    If (plngLeftIndex < lngLastIndex) Then Call QuickSortArrayOfStrings(pvarArrayToSort, plngLeftIndex, lngLastIndex)
    If (lngFirstIndex < plngRightIndex) Then Call QuickSortArrayOfStrings(pvarArrayToSort, lngFirstIndex, plngRightIndex)
End Sub

Views 1,332

Downloads 107