Guild Wars 2

I am trying to create a spreadsheet to calculate crafting values on the market, but so far the way it’s set up is…messy. If anyone out there is market savvy and excel savvy, I’d greatly appreciate a little input (no pun intended).

1. How would you suggest grouping items? Based on components or type? Example, as a jeweler, is it easier to group them based on jewelry type, (necklace earrings etc) or components to craft them (all rubies together, emeralds together, etc)

2. Is there a formula/code to link to the market’s prices? I’ve found sites that list the current prices on the BLTC, and instead of having to plug in prices manually, it’d be easier if I could just link the values to the spreadsheet.

3. Money values. Usually I convert the monetary values into silver for simplicity’s sake. But that makes 1 gold = 100 silver. Is there a format/formula that can accommodate gold/silver/copper?

4. Ectoplasm throws off my tables and gets stuffed in the middle of them. Usually I take the raw material pricing, convert them to their refinement, and keep calculating until it gets to the end product. Then I set up a formula to compare what’s the cheapest way to make an item. But ectoplasm is technically a raw material (not crafted) and not refined the same way as other materials. Is there a better/less cluttered way to set up a table that includes ectoplasm?

5. Sometimes components are worth more than the end product, so I also want to make a table for that too. Is there a way to keep them on one spreadsheet, or best to just separate them completely? The problem with separating each component from the end product, is it ends up with a LOT of pages. (and a lot of page flipping for linking formulas together). There has to be a less complicated way.

6. More marketing than spreadsheet: I haven’t included salvage material in my spreadsheets because simply…that’s a lot to work out. But might it actually be useful to consider buying stuff to salvage to get the raw materials?

7. Can you suggest a formula to account for mats already owned from gathering, and subtracting it from the total cost of production? I can guess buying everything, making it, and selling it back isn’t going to make much profit, so naturally I’ll need to farm some stuff myself. But should I subtract these already owned mats as raw products, or refined? Since these are also gathered, I’d also put in the cost of gathering tools, despite how small. But another question, can one strike (use) gather more than 1 material? If yes, how do you calculate for that?

8. I know there must be a ‘true/false/if’ formula in excel. I want to set up a ‘shopping list’ that can tell me what to buy off the market based on what’s cheapest. But I’m having trouble setting it up. I know how to set up the min value price but I can’t find the formula that links the min value price to the item. What is the formula called?

That should be everything for now. I’m not asking for someone to reveal their ‘marketing secrets’ to me, I just need help creating a spreadsheet. GW2’s system is complex…

It sounds like you might be trying to recreate this. If that’s not what you’re doing, can you elaborate a little on what you’re trying to calculate?

for how I think, I would recommend you entering all of the material’s cost and it tells you how much it would cost you to make all of the crafted items, adjusted for TP tax, then you search for the items on the TP and when you see one that sells for more than cost to make, you write it down. once done, buy your materials, make them, sell them.
3) just price everything in copper. 123456 copper = 12 gold, 34 silver, 56 copper.
5) maybe have column 1 be price to buy raw material, column 2 be price to make refined materials, column 3 be price to buy refined materials, column 4 be price to make components, column 5 be price to buy components, column 6 be price to make crafted item, column 7 be price to buy crafted item. you can probably use conditional formatting to have excel highlight (change background color) of any field that is cheaper than the one before it.
2)http://www.gw2spidy.com/api/v0.9/csv/all-items/all might be useful to you. I am curious if there is a better answer to that question

it’s not easy. that spidy page is probably good enough for the amount of work required.

btw: I already made my version of what you are doing took a long time. sorry, won’t give it away.

I’ll share the Script Editor functions for Google Docs that I currently use in my spreadsheets. Since you will have to resort to write stuff in JavaScript at one point or the other.

Note sure if I should just post the full thing here or post a link to it? Hm…
Ah well, I’ll leave a link, then you’ll have syntax highlighting and everything.

GW2 Spidy Functions

Also be sure to read Val’s document, titled Using GW2Spidy’s API to Update Your Spreadsheets in Google Drive

which you can find HERE

Plenty thanks for the help thus far. It’s been awhile since I played with anything code-related, but I’ll figure it out. The GW2Spidy was what I was referring to earlier, but I had something in mind that was more elaborate, and where to find a market niche.

Wish me luck in making money. Hah.