Using api on an excel spreadsheet?
Dagnis.2936:
So I’m starting to learn how to play the TP market and I thought of a cool idea that I’m sure others have thought of. Is it possible, using APIs to request to selling price of an item and place that data on excel, having it update every once in a while? I’m hoping that someone has already done this somewhere and would have a guide maybe on how to do it.
Killer Rhino.6794:
At this moment, there is no official trading-post API provide by ArenaNet (see the API documentation sticky’d at the top of this sub-forum for what is available).
The TP website, gw2spidy.com, might be a place to start looking for what you want. However, I don’t have the foggiest clue about Excel and its capabilities.
Heimdall.4510:
I think MS Query is the thing you’re looking for:
http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx
Hope this helps you solving your problem
Dr Ishmael.9685:
Gw2spidy’s API provides a CSV-style output that can be imported into Excel as a text datasource. Check their wiki for details and examples.
The official APIs are only available as JSON, and I don’t think there’s any way to import that directly in to Excel.
Illconceived Was Na.9781:
I’m finding more luck with guild wars trade.
- New items are added more often.
- The response time is better.
- Spidy is down or wigs out during a data request; gwt has always worked for me.
- Spidy is still pulling incorrect data from GW2DB, which miscategorizes items (probably about 10%).
Although spidy provides about 10 fields versus only 4 for GWT, most of the spidy data is static (and, as noted above, not always accurate).
tl;dr I used to import spidy data into excel, now I use guildwarstrade.com and I greatly prefer it for my purposes.
To learn how to import csv data into excel, use the link provided by Dr Ishamel.9685. There is a PDF that explains how to do it for Spidy and you can substitute the appropriate URL for Guild Wars Trade. The excel interface is unintuitive.
cvpcs.5914:
If you’re using a newer version of Excel you can accomplish this with the GW2Spidy API combined with the WEBSERVICE functions. About 6 months ago I submitted a series of patches to provide XML API support specifically so Excel could work with spidy.
Usually what I do is have a hidden column for the item IDs, a column for the XML to download into, and then filter it from there into the various fields. For example
- A1 is my ID field for Mithril Ore, it will contain
19700 - B1 is my data field, so it will contain
=WEBSERVICE(CONCATENATE("http://www.gw2spidy.com/api/v0.9/xml/item/", A1, "?excel_filterxml_fix=1")) - C1 is my value field where I want to place the current buy price, so it will contain
=FILTERXML(B1, "/response/result/max_offer_unit_price")
You’ll notice that I append ?excel_filterxml_fix=1 to the end of my API request. This solves a very specific issue with Excel, where 4 digit numbers between 1900 and 9999 that are returned are interpreted as years, and the value displayed in the cell becomes the number of days since January 1st, 1900. This is a known bug in Excel. This addition to the end of the request URL triggers a workaround, which can be seen here
KU Jayhawk.5803:
Is there any way to export your personal Trading Post to Excel such as what you’ve bought and sold and are buying and selling? I’d like to create a balance sheet to know what I’ve currently made given that so much money gets tied up in the flux between it all.
DarkSpirit.7046:
Is there any way to export your personal Trading Post to Excel such as what you’ve bought and sold and are buying and selling? I’d like to create a balance sheet to know what I’ve currently made given that so much money gets tied up in the flux between it all.
The short answer is no. To access your personal Trading Post transactions, you need to have your GW2 client session key.
However, there are apps that read the session key from your GW2 client memory and displays your personal transactions for you. For example, this:
The app is also open source. I am not 100% sure if this is considered a violation from ArenaNet’s perspective since we have asked them several times without getting a reply from them, so use it at your own risk.
I wish ArenaNet would provide an official trading post api with OAuth2 support though, so that we don’t have to resort to reading the session key off the client memory.
DarkSpirit.7046:
I’m finding more luck with guild wars trade.
- New items are added more often.
- The response time is better.
- Spidy is down or wigs out during a data request; gwt has always worked for me.
- Spidy is still pulling incorrect data from GW2DB, which miscategorizes items (probably about 10%).
I need to say something about the use of the data from GW2DB. I agree that GW2DB does have some outdated data but gw2spidy has already updated to use the official items/recipes api.
http://www.reddit.com/r/Guildwars2/comments/1m96er/gw2spidy_updated_with_ascended_recipes/
I suspect that gw2spidy still needs the GW2DB because the official items/recipes api doesn’t have vendor information. You can’t tell which vendors sell this particular item at what gold cost or karma cost from the official api, even though I have requested for this a long time ago. GW2DB has this information.
Without this information, gw2spidy would not be able to generate the recipe minimum crafting cost tree with the cost of buying from vendor like this:
http://www.gw2spidy.com/recipe/3258
My app is currently in the same predicament, it uses BOTH the GW2DB and the official items/recipes api as it generates recipe trees like the ones from gw2spidy except that I had to create my own DB for mystic forge recipes and also includes skill point costs for some of the items. I have suggested these to be included in the official items/recipes api as well.