Register
Page 1 of 2 12 Last
Results 1 to 10 of 12
  1. #1
    Attica's Avatar
    Posts
    19
    Reputation
    0
    Tagged in
    0 Posts
    Add to this user's reputation

    Undermine Journal Data Extraction

    I use The Undermine Journal on a daily basis to track my sales when I'm not online, spy on my competition, analyze the market value of items to decide when is the best time to buy/sell, etc.

    I also use an Excel spreadsheet to help me decide what materials I should buy for my profession and what crafted item is most profitable. Presently my data is manually inputted, but I am trying to use the Import External Data option to update using your website. My problem is the calculations are failing due to how the data is laid out to include the gold, silver, and copper tags (i.e. 2211g 55s 96c). Do you happen to know of a way to remove the tags to perform a standard calculation (i.e. 2211.5596).

    I was hoping to make this spreadsheet as automated as possible so anyone could use it, then I would share it as others have shared theirs.

    I understand this may be out of your realm of expertise, but I appreciate any guidance you can provide. Thank you.

  2. #2
    Unregistered
    Unregistered's Avatar

    Excel ftw

    This is a roundabout way of doing it, but if it comes in as one string by itself, you could try using excel's "LEFT" and "MID" functions, then use the FIND function inside of those to separate the g, s and c values into separate cells.

    I'll explain: you'll have 4 cells per row. One with the original string (2211g 55s 96c), and the next 3 with the amount of gold, silver and copper.

    Put this in the "gold" cell: =LEFT(C5,FIND("g",C5,1)-1)
    Put this in the "silver" cell: =MID(C5,FIND("s", C5,1)-2,2)
    Put this in the "copper" cell: =MID(C5,FIND("c", C5,1)-2,2)

    You can then add another (5th) cell if you like to get 2211.5596 if you divide silver and copper by 100 and 10000 and add that to the gold.

    By the way, can you tell me how to export TuJ data? I'm not familiar with "Import External Data" option.

    Thanks

  3. #3
    Attica's Avatar
    Posts
    19
    Reputation
    0
    Tagged in
    0 Posts
    Add to this user's reputation
    Data > Import External Data > New Web Query > Web Address > Go > Select Table > Import

    The issue with using this option with TUJ is the data is technically not in a table, so my only viable option is to import the entire page of the item(s) I want to import. That said, I may create a separate spreadsheet with the specific purpose of querying and updating data, then have my main spreadsheet reference it.

    This may be different depending upon what version of Excel you're using. Mine is a bit outdated. (2003)

    I'm not sure if having more/less digits in the values will affect your formulas, so I'm going to give it a try and see how it works. Thanks very much for your reply.

  4. #4
    Attica's Avatar
    Posts
    19
    Reputation
    0
    Tagged in
    0 Posts
    Add to this user's reputation
    It works beautifully. This may take some time to properly implement, but I think I'm on the right track now.

    It looks like Pliaksi's Goblin Crafter Spreadsheet ultimately does what I want to achieve, except it extracts from your Auctioneer lua file while I want to use TUJ data.

  5. #5
    Erorus's Avatar
    Posts
    1,404
    Reputation
    114
    Tagged in
    677 Posts
    Add to this user's reputation
    Don't get too attached to this. Those web pages are meant to be viewed in a browser, not parsed by crawlers. I like finding ways to break crawlers, since unchecked they can degrade the quality of service for other web visitors.

    Wait for the API. I'll have current market value, current qty available, avg market price, std dev of market price, and date last seen in a couple weeks in XML format.

  6. #6
    Whoa, seriously??? Awesome! If I'd known you were working on this I wouldn't have tried messing with the lua in the addon to try to decompress the stream into something I could play with.

    Always a silent step ahead, Erorus.
    Last edited by Stede; March 10th, 2011 at 04:47 PM.
    Retired - I blame Kathroman for everything.

    (that's a joke, eh)

  7. #7
    Attica's Avatar
    Posts
    19
    Reputation
    0
    Tagged in
    0 Posts
    Add to this user's reputation
    That's really exciting news! I'm glad I asked my question here to learn this. Hopefully I get my spreadsheets all fixed up to be ready to benefit from this transition. I really appreciate everything the workers on TUJ has provided.

  8. #8
    Kathroman's Avatar
    Posts
    5,452
    Reputation
    286
    Blog Entries
    20
    Tagged in
    2157 Posts
    Add to this user's reputation
    Quote Originally Posted by Erorus View Post
    Don't get too attached to this. Those web pages are meant to be viewed in a browser, not parsed by crawlers. I like finding ways to break crawlers, since unchecked they can degrade the quality of service for other web visitors.

    Wait for the API. I'll have current market value, current qty available, avg market price, std dev of market price, and date last seen in a couple weeks in XML format.
    XML for dummies might have just made another sale...lol. That's great news Erorus. I'm sure there will be many people getting excited about this.

    Thanks again for all the work you have done with TUJ so far!
    Like what you see? Become an Ethereal Contributor to help support the site and gain access to additional perks.

        

    Guides: Thunder Bluff Vendor Supply Route | Entry Level JC Strategy | Alternative DMF Guide

    TSM 2.0: TSM 2.0 Groups & Operations GUIDE | TSM 2.0 Basic JC Shuffler | TSM 2.0 ADVANCED JC Shuffler

  9. #9
    unp's Avatar
    Posts
    132
    Reputation
    17
    Tagged in
    37 Posts
    Add to this user's reputation
    You're better off using an add on that tracks sales to import into excel. TUJ is a great resource but is fairly inaccurate at recording sales. When I look at my guys I see sales that were actually me canceling and tons of sales that weren't recorded at all

  10. #10
    Erorus's Avatar
    Posts
    1,404
    Reputation
    114
    Tagged in
    677 Posts
    Add to this user's reputation
    That's why there won't be any sales data in the XML.

 

 

Similar Threads

  1. Undermine Journal Data Extraction
    By Attica in forum Archive (Addons and Macros)
    Replies: 0
    Last Post: March 10th, 2011, 01:16 PM