ゆゆのExcelVBA覚書-副業から独立-

現役社内SEがExcel VBAを中心に覚書として書いていくブログです。

トップ > Amazon欧米輸入 > 【MWS API】ASINからカート価格を取得しよう【Excel VBA】

【MWS API】ASINからカート価格を取得しよう【Excel VBA】

Amazon欧米輸入 Excel VBA

Hatena

今回はかなり実用的な、ASINからカート価格の取得方法について記事にしていきます。

カート価格とは
　ショッピングカートボックスの価格。
ショッピングカートボックスとは、
Amazonサイトの商品詳細ページに表示されているカートに入れるボタン。
購入者が商品を自分のショッピングカートに入れることにより、購入手続きが開始される仕組みになっています。

Amazon公式ではこのように書かれています。

Amazonは一つの商品ページに複数人出品できるため、このような仕組みがあります。

このカート価格に合わせて価格を決めないと、出品した商品が全然売れないためとても重要な項目です

カート価格を取得する前に

以前解説した時に使用したファイルに追加する形になりますので、
まだ読んでいない方はご覧ください。
www.yumeigunshi.com

ASINからカート価格を取得するコード

前回記事の接続確認をしたファイルに、下記を追加してください。 丸コピで動くと思います。 解説はそのあとに書きます。

Excelシート

f:id:yumeigunshi444:20190914231142p:plain
カート価格を取得したいASINを記載
f:id:yumeigunshi444:20190914232432p:plain
取得結果を貼り付ける「Result」シート

VBAコード

「ASIN」シート

Option Explicit

Private Sub BTN_実行_Click()
    Dim xml As Object
    Dim dic As Object
    
    Dim lstRow As Long
    Dim asinRow As Long
    Dim resultRow As Long
    Dim result() As Variant
    Dim asinList As Collection
    
    Dim asin As String
    Dim shohinObj As Object
    Dim itemObj As Object
    Dim wkObj As Object
    Dim i As Integer, j As Integer
    Dim param As String
    Dim wk As Variant
    
    Dim asinSort() As String
    Dim strSort As String
    strSort = "1,10,2,3,4,5,6,7,8,9"
    asinSort = Split(strSort, ",")
    
    '最終行取得
    lstRow = Cells(Rows.Count, 1).End(xlUp).Row
    If lstRow = 1 Then
        MsgBox ("ASINが入力されていません。")
        Exit Sub
    End If
    
    '初期処理
    Set dic = CreateObject("Scripting.Dictionary")
    
    asinRow = 2     '開始行数
    
    'Result配列の宣言
    ReDim result(1 To lstRow - 1, 1 To 8)
    
    While asinRow <= lstRow
        Set asinList = New Collection
        With Sheets("ASIN")
            ' ASINを取り出す
            j = 0
            Do While j < 10
                ' 最終行の場合、処理終了
                If asinRow > lstRow Then
                    Exit Do
                End If
                ' 情報をセット
                asin = CStr(.Cells(asinRow, 1).Value)
                If dic.exists(asin) = False Then    '未処理のASINを処理
                    Set wk = New Collection
                    wk.Add asin, "ASIN"
                    wk.Add asinRow - 1, "ROWS"
                    asinList.Add wk, CStr(asinList.Count + 1)
                    
                    '処理済ASINを格納
                    dic.Add asin, asinRow
                    j = j + 1
                End If
                
                '配列初期化
                result(asinRow - 1, 1) = asin   'ASIN
                result(asinRow - 1, 2) = "-"    'カート価格
                result(asinRow - 1, 3) = "-"    'ポイント
                result(asinRow - 1, 4) = "-"    '例外
                ' 次行へ
                asinRow = asinRow + 1
            Loop
        End With
        
        param = ""
        ' ASINを1つずつ取り出す
        For i = 0 To UBound(asinSort) - 1
            j = 1
            For Each wk In asinList
                If j = CLng(asinSort(i)) Then
                    If j = 1 Then
                        param = "ASINList.ASIN." & j & "=" & wk("ASIN")
                        
                    Else
                        param = param & "&ASINList.ASIN." & j & "=" & wk("ASIN")
                    End If
                    
                    Exit For
                End If
                j = j + 1
            Next
        Next
        
        ' リクエストを作成
        param = MakeParam(param)
    
        Set xml = getXMLPost("https://mws.amazonservices.jp/Products/2011-10-01", param)
        '情報取得
        Call GetShohinInfo(xml, result, asinList)
        
        
        '１秒のウェイト
        Sleep 1000
    Wend
   
    'Resultへセット
    With Sheets("Result")
        'クリア
        resultRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range(.Range("A2").Address, .Cells(resultRow, 4).Address) = ""
        '貼り付け
        .Range(.Range("A2").Address, .Cells(lstRow, 4).Address) = result
    End With
    
    MsgBox "出力完了"
End Sub


'パラメータ作成
Function MakeParam(asinList As String) As String
    Dim param As String
    Dim paramToSign As String
    Dim sellerId As String
    Dim accessKey As String
    Dim seacretKey As String
    Dim endPoint As String
    Dim marketPlaceId As String
    Dim timeStamp As String
    Dim apiVersion As String
    Dim sign As String
    Dim url As String
    
    'パラメータの取得
    With Sheets("MWS")
        sellerId = .Range("B1").Value
        accessKey = .Range("B2").Value
        seacretKey = .Range("B3").Value
    End With
    endPoint = "mws.amazonservices.jp"
    marketPlaceId = "A1VC38T7YXB528"
    timeStamp = Format(CDate(DateAdd("h", -9, Now)), "yyyy-mm-dd") & "T" & Format(CDate(DateAdd("h", -9, Time)), "hh%3AMM%3Ass") & "Z"
    apiVersion = "2011-10-01"
    
    ' リクエストを作成
    param = asinList _
            & "&AWSAccessKeyId=" & accessKey _
            & "&Action=GetCompetitivePricingForASIN" _
            & "&ItemCondition=New" _
            & "&MarketplaceId=" & marketPlaceId _
            & "&SellerId=" & sellerId _
            & "&SignatureMethod=HmacSHA256" _
            & "&SignatureVersion=2" _
            & "&Timestamp=" & timeStamp _
            & "&Version=" & apiVersion
            
    paramToSign = "POST" & vbLf & endPoint & vbLf & "/Products/2011-10-01" & vbLf & param
    sign = GetSign(seacretKey, paramToSign)
    
    param = param & "&Signature=" & sign

    MakeParam = param
End Function

'結果XMLから商品情報を取得
Sub GetShohinInfo(xml As Object, result() As Variant, asinList As Collection)
    Dim getProObj As Object
    Dim shohinObj As Object
    Dim wkObj As Object
    Dim strAsin As String
    Dim price As Double
    Dim points As Double
    Dim reigai As String
    
    Dim resultRow As Long
    Dim wk As Collection
    Dim k As Integer
    
    
    Set getProObj = xml.SelectNodes("GetCompetitivePricingForASINResponse/GetCompetitivePricingForASINResult")

    ' 取得したProductオブジェクトを順次取り出し
    For k = 0 To getProObj.length - 1
        '初期化
        strAsin = ""
        price = 0
        points = 0
        reigai = "-"
        
        'ASIN 取得
        For Each wkObj In getProObj(k).Attributes
            If wkObj.Name = "ASIN" Then
                strAsin = wkObj.Value
            End If
        Next wkObj
        
        ' ASINが取得できていれば処理を行う。
        If strAsin <> "" Then
            
            'エラーの場合
            Set wkObj = getProObj(k).SelectNodes("Error/Message")
            If wkObj.length <> 0 Then
                reigai = wkObj(0).text
            Else
                '商品情報
                Set shohinObj = getProObj(k).SelectNodes("Product/CompetitivePricing/CompetitivePrices/CompetitivePrice")
                If shohinObj.length <> 0 Then
                    ' カート価格を取得
                    Set wkObj = shohinObj(0).SelectSingleNode("Price/LandedPrice/Amount")
                    If Not wkObj Is Nothing Then
                        price = wkObj.text
                    End If
                    'ポイントを取得
                    Set wkObj = shohinObj(0).SelectSingleNode("Price/Points/PointsMonetaryValue/Amount")
                    If Not wkObj Is Nothing Then
                        points = wkObj.text
                    End If
                    
                End If
            End If
            
            '情報のセット
            With Sheets("Result")
                '行数取得
                Set wk = asinList(CStr(k + 1))
                resultRow = wk("ROWS")
                
                If price <> 0 Then
                    result(resultRow, 2) = Int(price)
                    result(resultRow, 3) = Int(points)
                End If
                result(resultRow, 4) = reigai
            End With
        End If
    Next k
End Sub


実行結果

f:id:yumeigunshi444:20190915000637p:plain
ASINを記載して「実行」ボタンを押す
f:id:yumeigunshi444:20190915000832p:plain
取得結果

GetCompetitivePricingForASINの解説

使用するアクションから解説していきます。

カート価格を取得するアクションです。
今回はカート価格とポイントを取得しています。

スロットリング

f:id:yumeigunshi444:20190915001605p:plain 1秒ごとに10商品取得できるようになるので、
今回は一度のリクエストで10個のASINを送信します。

リクエストパラメーター

必須パラメーターはMarketplaceIdとASINListです。

MarketplaceIdはこれまで出てきたものと同じものです。
ASINListはカート価格を取得したいASINをリストで指定します。

指定の仕方は後述のVBAの解説でします。

VBAコード

実は、前回記事の商品情報取得からちょっと変えただけでできてしまいます。
www.yumeigunshi.com
なので違うところを中心に解説していきます。

ASINのソート

    Dim asinSort() As String
    Dim strSort As String
    strSort = "1,10,2,3,4,5,6,7,8,9"
    asinSort = Split(strSort, ",")

strSortに入れた文字列をカンマ区切りで配列にセットしています。
これはなんなのかというとパラメーターの並び替えの為です。


今回はASINを1度に10個指定するので、並び替えが必要になります。
パラメーターで送信する際には、下記の用に表記します。

ASINList.ASIN.1.BXXXXXXXXX
ASINList.ASIN.2.BXXXXXXXXX
ASINList.ASIN.3.BXXXXXXXXX
ASINList.ASIN.4.BXXXXXXXXX
ASINList.ASIN.5.BXXXXXXXXX
ASINList.ASIN.6.BXXXXXXXXX
ASINList.ASIN.7.BXXXXXXXXX
ASINList.ASIN.8.BXXXXXXXXX
ASINList.ASIN.9.BXXXXXXXXX
ASINList.ASIN.10.BXXXXXXXXX

しかし、これではエラーになってしまいます。
1～10で順番通りに並んでますが、数値順ではなく文字列順に並び替える必要があります。

<正>
ASINList.ASIN.1.BXXXXXXXXX
ASINList.ASIN.10.BXXXXXXXXX
ASINList.ASIN.2.BXXXXXXXXX
ASINList.ASIN.3.BXXXXXXXXX
ASINList.ASIN.4.BXXXXXXXXX
ASINList.ASIN.5.BXXXXXXXXX
ASINList.ASIN.6.BXXXXXXXXX
ASINList.ASIN.7.BXXXXXXXXX
ASINList.ASIN.8.BXXXXXXXXX
ASINList.ASIN.9.BXXXXXXXXX

この並び替えを行うために定義しています。

パラメーターの作成

    ' ASINを1つずつ取り出す
    For i = 0 To UBound(asinSort) - 1
        j = 1
        For Each wk In asinList
            If j = CLng(asinSort(i)) Then
                If j = 1 Then
                    param = "ASINList.ASIN." & j & "=" & wk("ASIN")
                    
                Else
                    param = param & "&ASINList.ASIN." & j & "=" & wk("ASIN")
                End If
                
                Exit For
            End If
            j = j + 1
        Next
    Next

先ほどのASINListのパラメーターの作成をしています。

    ' リクエストを作成(パラメータはアルファベット順なので順番を変えるとNG)
    param = asinList _
            & "&AWSAccessKeyId=" & accessKey _
            & "&Action=GetCompetitivePricingForASIN" _
            & "&ItemCondition=New" _
            & "&MarketplaceId=" & marketPlaceId _
            & "&SellerId=" & sellerId _
            & "&SignatureMethod=HmacSHA256" _
            & "&SignatureVersion=2" _
            & "&Timestamp=" & timeStamp _
            & "&Version=" & apiVersion

そして結合します。

商品情報の取得とほぼ変わりません。

カート価格の取得

スクラッチパッドでレスポンスを確認して作っていきます。
f:id:yumeigunshi444:20190915005208p:plain
3行目のGetCompetitivePricingForASINResultタグで1つの商品となっています。

XML

  <GetCompetitivePricingForASINResult ASIN="B079CDTFS3" status="Success">

このタグにASINが入っているので取り出します。

    'ASIN 取得
    For Each wkObj In getProObj(k).Attributes
        If wkObj.Name = "ASIN" Then
            strAsin = wkObj.Value
        End If
    Next wkObj

これで取得できればカート価格を取りに行きます。

カート価格はLandedPrice。

XML

            <Price>
              <LandedPrice>
                <CurrencyCode>JPY</CurrencyCode>
                <Amount>6915.00</Amount>
              </LandedPrice>

ポイントはPointsMonetaryValue。

XML

              <Points>
                <PointsNumber>3</PointsNumber>
                <PointsMonetaryValue>
                  <CurrencyCode>JPY</CurrencyCode>
                  <Amount>3.00</Amount>
                </PointsMonetaryValue>
              </Points>

ちなみにポイントが設定されていない場合は、この項目は出てきません。

これを下記VBAコードで取得。

    '商品情報
    Set shohinObj = getProObj(k).SelectNodes("Product/CompetitivePricing/CompetitivePrices/CompetitivePrice")
    If shohinObj.length <> 0 Then
        ' カート価格を取得
        Set wkObj = shohinObj(0).SelectSingleNode("Price/LandedPrice/Amount")
        If Not wkObj Is Nothing Then
            price = wkObj.text
        End If
        'ポイントを取得
        Set wkObj = shohinObj(0).SelectSingleNode("Price/Points/PointsMonetaryValue/Amount")
        If Not wkObj Is Nothing Then
            points = wkObj.text
        End If
        
    End If

そして2次元配列へセット。

    '情報のセット
    With Sheets("Result")
        '行数取得
        Set wk = asinList(CStr(k + 1))
        resultRow = wk("ROWS")
        
        If price <> 0 Then
            result(resultRow, 2) = Int(price)
            result(resultRow, 3) = Int(points)
        End If
        result(resultRow, 4) = reigai
    End With

これにて取得完了です！

まとめ

カート価格は取得できたでしょうか？

分かりにくいところなどあれば、コメントやTwitterでご連絡ください。