Results 1 to 10 of 10

Thread: My Spreadsheet

  1. #1

    My Spreadsheet

    Due to some demand, I'm posting the current iteration of my spreadsheet. It spins the data available in the very cool TUJ XML export in a variety of different ways to suit my specific needs.

    • It's Excel 2010. It won't work in OpenOffice. It might not work in Excel 2007. It won't work in any earlier version of Excel.
    • It uses some VBA code, which will hopefully be disabled when you initially open it. Look over the code to make yourself happy there are no bad things in it, then enable it. Or don't, but it won't do anything without code.
    • I wrote this for me so it only has the crafts I'm interested in right now. If you want to add crafts to it, follow the pattern (more info below). I will answer questions posted to this thread as I am able.
    • I am in no way responsible for anything that happens to you, your computer, or your WoW gold if you download this thing. It's all on you.

    Using It
    1. Open your TUJ XML feed in your browser, then save it as a file on your computer somewhere.
    2. Go into the "TUJ XML" worksheet, then navigate into Excel's Data menu, then click the "From Other Sources" button, then load up the file you saved in the first step.
    3. Go into the "Control" worksheet, then click the "Populate GP Values & Qty" button. Wait a minute or two (depends on the speed of your computer). At this point, all sheets but Analysis should have accurate data for you to look at.
    4. Still on the "Control" worksheet, edit the values in C19:E21 as you see fit (the Analysis table).
    5. Click the Analysis button and wait a bit, probably about 30 seconds but it really depends on the configuration you did in the step above. Lower profit threshholds mean you'll get more stuff on the Analysis table. Higher profit threshholds mean you'll see less stuff. More stuff = more time.
    6. Look at the Analysis worksheet.

    Adding Items to Existing Sheets
    You can add items to existing worksheets. Here's how.
    1. Navigate to the worksheet you want to add the item into. "Int Mats" is short for intermediate materials - stuff you make that you make stuff with. For Tailors, think Dreamcloth. For Inscribers, think Pigments and Inks. The key to Int Mats is that it uses GP values from the raw materials, not from the TUJ feed. Anyway, now you're on the sheet you want to add the item into.
    2. Insert a row wherever you want it on that sheet.
    3. Populate Column B with the item's name as it is ingame. Exactly as it is ingame. Or in the TUJ item (not price) feed, I guess, because that worksheet drives a future step.
    4. Go to the Control worksheet and type the name of the worksheet you just added into the "B7" cell and click the Populate Item IDs button.
    5. Back to the sheet you're adding the item into, and you'll see it now has an Item ID in Column A. If it doesn't, either you munged the name or the TUJ Item Feed ("Item Ref" worksheet) doesn't know about it. You need the right Item ID in Column A. Figure out how to get it there.
    6. What you do next depends on the kind of sheet you're populating...

    Populating the Raw Mats Sheet
    This assumes you did the stuff in Adding Items to Existing Sheets above.
    1. Go into the Control sheet and type your sheet's name into the B12 cell. Click the Set Ref Names button.
    2. Click the Populate GP Values & Qty button.

    Populating the Int Mats Sheet
    This assumes you did the stuff in Adding Items to Existing Sheets above. This sheet tracks intermediate materials. I didn't want to put them into the profession-specific sheets because I thought it would be easier this way. Maybe it is.
    1. Do the stuff under Populating the Raw Mats Sheet, but for this sheet. Do this first.
    2. Use Excel to pouplate Column C with the right formula for the intermediate material. You'll reference cells on the Raw Mats and Int Mats sheet to do this.

    Populating a Profession Sheet
    This assumes you did the stuff in Adding Items to Existing Sheets above.
    1. Copy the values in columns E through H on the row immediately above or below the one you're inserting into your row.
    2. Type the number of raw mats into the raw mat columns on your row.
    3. If the sheet doesn't already know about the raw mat(s) you need, you need to add them. Make sure every column you add for raw mats on profession sheets is no more than "4" wide (same width as the current ones). You'll also have to update the formula in column F to include the value for the new raw material. Just follow the pattern.
    4. Click the Populate GP Values & Qty button.

    Adding New Profession Sheets
    Follow the pattern for existing Profession sheets. You're also going to have to edit code - add your sheet to the PopulateAllGPValues() function and your sheet name to the sheet name constants at the top of the VBA file.

    Deleting Rows
    If you have no interest in rows in sheets, you can delete them. This is important: DELETE THE ROW. Do not delete the values. To delete the row, right click on the row header and choose Delete.

    Be very very careful deleting rows in the Raw Mats and Int Mats sheets - if they're referenced by something in the Profession sheets, you'll break formulas and it will get messy.

    The Link
    This link will be active for 48 hours, so I'll take it down around 2:00 Eastern on Wednesday 3/30.

    Last edited by thc1967; March 28th, 2011 at 02:24 PM. Reason: Added instructions for deleting rows.

  2. #2
    brandonh6k's Avatar
    Tagged in
    3 Posts
    Add to this user's reputation
    Quick FYI - works fine in Excel 2007 so far (at least i was able to get realm data imported and an analysis run). Thanks!

  3. #3
    Erorus's Avatar
    Tagged in
    677 Posts
    Add to this user's reputation
    Thanks for sharing, pretty cool.

  4. #4
    So I've done some clean-up on the code to make it more efficient and added more control options and added another page. The page analyzes inks - as in it tells me whether I should make or buy inks. It also tells me how many of my component items are available and the market price for each, sorted ascending by price.

    Would that be useful or are enough of you using a tool ingame that gives you real-time answers to that question? (I still need to figure out TSM...)

  5. #5
    Aeg's Avatar
    Tagged in
    234 Posts
    Add to this user's reputation
    I much prefer this spreadsheet, as the ingame tools are only as valuable as your data which depends on your scans for the most part. I havent had a chance to really sit down and look through it all for my server but it is on my to do list. Thanks again though for sharing this with us.
    Experience for mining/herbing - Making farming tolerable and leveling painful.
    There can only be one Rewind Trader

  6. #6
    OK. Few more tweaks and I'll post an update tomorrow.

    Edit: OK, not going to do it. TSM > this spreadsheet. By a lot. The spreadsheet is interesting for analyzing the AH when I'm not ingame, but when it comes time to craft and get the best and most accurate prices on mats, TSM is the nuts.
    Last edited by thc1967; March 29th, 2011 at 08:29 PM.

  7. #7
    brandonh6k's Avatar
    Tagged in
    3 Posts
    Add to this user's reputation
    I guess that just reinforces that I need to buckle down and figure out how to use TSM. :P

    Thanks for the spreadsheet, thc.

  8. #8
    Yah, TSM seemed difficult at first. Then I read the tutorial here in these forums... twice... then used it to walk through setting mine up slowly and carefully.

    I'm still learning, but it's already saved me a ton of time and made me gold. Well worth the intellectual investment.

  9. #9

    reimporting data?

    I followed your instructions the other day to set this up, then just now I went to update the data but all of the "get external data" options are grayed out now.

    Any ideas?

  10. #10
    So here's what I do. I've saved the TUJ XML feed to a specific file on my local drive (as I'm sure you did). When I want to update, I get the refreshed TUJ XML feed in my browser and save it again into the exact same file. Then I flip over to the TUJ XML worksheet in the workbook, go to Excel's Data tab, click the arrow under the "Refresh All" button, and choose "Refresh" from the menu.

    Alternatively, you can select everything on your TUJ XML tab and delete it, then start from scratch with the instructions for loading data in the original post.



Similar Threads

  1. Shuffle Spreadsheet 2.0
    By Kathroman in forum Archive (Professions)
    Replies: 10
    Last Post: July 25th, 2012, 01:26 AM
  2. DE Spreadsheet
    By Kathroman in forum Archive (Professions)
    Replies: 11
    Last Post: May 9th, 2011, 12:59 PM
  3. LW Disenchanting Spreadsheet
    By Kathroman in forum Archive (Professions)
    Replies: 4
    Last Post: March 17th, 2011, 02:35 PM
  4. JC/Shuffle Spreadsheet?
    By Namssob in forum Archive (Professions)
    Replies: 12
    Last Post: January 9th, 2011, 09:59 PM