Using XML spreadSheet to get the price of an item

Question

I want to get the price of an item which is market in steam. I tried to use this formula but it is not working it tells me that the value is too big. and I did not know what to do. I want to get the price of an item which is on market on steam.

Blockquote =VALUE(REGEXEXTRACT(REGEXEXTRACT(CONCATENATE(IMPORTXML("https://steamcommunity.com/market/listings/730/Clutch%20Case", "//script[2]")),".*]]"), "[0-9]+.[0-9]+"))

Daniel · Accepted Answer · 2022-05-24T00:20:45.060

The main problem here is that the prices in the Steam page are generated by Javascript and IMPORTXML cannot retrieve dynamically generated data. It seems that you're trying to get around this by importing a <script> section, but this will not execute the script, you're just grabbing a bunch of code.

According to this answer, Steam has some endpoints that you can use to get market data. These return a simple JSON string with the item information. The endpoint looks like this:

http://steamcommunity.com/market/priceoverview/?currency=1&appid=[ID]&market_hash_name=[Item name]

The appid is the game's ID, and the market_hash_name is the URL-encoded name of the item. Conveniently you can already find these in the URL that you are already using, https://steamcommunity.com/market/listings/730/Clutch%20Case. The game ID is 730 and the name is Clutch%20Case. So you can plug these in to the endpoint to get this URL:

http://steamcommunity.com/market/priceoverview/?currency=1&appid=730&market_hash_name=Clutch%20Case

The endpoint's JSON looks like this:

{
   "success":true,
   "lowest_price":"$0.30",
   "volume":"94,440",
   "median_price":"$0.31"
}

Since you only care about the median price, we can use a formula with REGEXEXTRACT to extract only that part:

Here's a sample pasting the URL in A1.

=REGEXEXTRACT(JOIN("", IMPORTDATA(A1)), "median_price:""(\$[0-9]+.[0-9]+)")

Edit: As mentioned in the answer I linked, you can test the currency parameter in the URL with different numbers to get other currencies. In your case you can try currency=2for pounds (£). You'll also have to edit the REGEXEXTRACT to account for this change:

URL: http://steamcommunity.com/market/priceoverview/?currency=2&appid=730&market_hash_name=Clutch%20Case

Formula: =REGEXEXTRACT(JOIN("", IMPORTDATA(A1)), "median_price:""(£[0-9]+.[0-9]+)")

Thank you it works fine. Just is any chance to change to the £ instead of $. Thank you so much. — , May 23 '22 at 23:47
Also, when I try to multiply the price I am getting this Function MULTIPLY parameter 2 expects number values. But '$0.10' is a text and cannot be coerced to a number. — , May 23 '22 at 23:52
I edited my answer with a way to get the values in pounds. The `MULTIPLY` function coerces the text to numbers depending on your locale, so it should work after you change it to £, but you'll probably have to retype the function. For me it's the other way around, it works for $ but not for £. — Daniel, May 24 '22 at 00:23
I do have an error "Function REGEXEXTRACT parameter 2 value "median_price:"(£[0-9]+.[0-9]+)" does not match text of Function REGEXEXTRACT parameter 1 value "{"success":truelowest_price:"$0.10"volume:"39033"median_price:"$0.11"}"." — , May 24 '22 at 15:13
It doesn't look like you've changed the URL. You need to change it to `currency=2` so Steam returns the price in pounds. — Daniel, May 24 '22 at 15:25

Using XML spreadSheet to get the price of an item

1 Answers1