1. Optimizing Gold Buying on Warmane

    Hi all! I'm not sure why, but the trade section on Warmane does not list the gold gained per coin spent in the Trade section and that has always bothered me.

    Today, I decided to use Google Sheets to remedy this.

    If you're an over-optimizer like me, the kind of person who regularly busts out a calculator at the store to ensure your purchases are the most cost-efficient as they can be, then this guide is for you.

    I navigated to the Trade section, selected the text such that my clipboard looked like this:

    [icon] 2121 gold Long <user> 4 coins

    and hopped into a fresh sheets page. I pasted the results and realized it was time to use regular expressions to get this data into a usable format, as this is what I had, including column IDs: (formatting is lost below, so be aware that columns E, G, and H are empty)

    A B C D E F G H
    [icon] 2121 gold Long <user> 4 coins

    In sheets, you can use the REGEXEXTRACT function to achieve our goal. The gold total we need is in column B, but it has the string " gold" in the way of being usable. To normalize this data, I used the following:

    =REGEXEXTRACT(B1,"\d\d\d\d")

    in cell E1, the first empty cell. This little wizardry says, "Use the function regex-extract on cell b1 and what i'm looking for is anything that matches 4 digits in a row while ignoring everything else." The output of this function in cell E1 is 2121 in our example.

    Similarly, to get the number of coins only, we use:

    =REGEXEXTRACT(F1,"\d")

    in cell G1. This puts a nice, usable number 4 in cell G1. From there, it's a simple matter of using our two newly created cells in division to get the results:

    =E1/G1

    That goes into H1 and shows 530.25 gold per coin spent. Great! For the sample I tested, the range of gold per coin was 480 at the low end to 780 at the high end. Definitely worth a few minutes to find out that you could get nearly twice as much gold per coin, eh?

    You can of course grab large numbers of entries, rather than just one at a time, and then sort by column H to see which entry will give you the most gold per coin spent.

    CAVEAT

    Please be aware that if the listing has more than 4 numbers for the gold amount, such as 10,000 gold, or more than 1 number for the coins, such as 10 coins, you will need to modify how many times the E and G cells have a \d listed. Because yes, each \d stands for 1 digit. Hopefully that much was already clear but if you're new to regular expressions (regex) that may not have been obvious.

    If you've never used excel or sheets before, please be aware that you can get your formulas set up in the top row and then select the cell and drag down to apply the same formula to all the cells beneath it, rather than copy/pasting repeatedly.

    If you found this guide helpful and you're new to spreadsheets, take some time to familiarize yourself with them. They're quite powerful, not as confusing as they initially look, and can make you look like some kind of space-brain genius to your excel-ignorant boss. ;)

  2. does excel regex not have [0-9]{3,6} functionality? (e.g. capturing 3 to 6 numbers in the range of 0-9)



    edit: just spent a few minutes playing with regex and:

    ([0-9]{3,6}) gold (Long|Short|Medium) [A-Za-z]+ ([0-9]+) coins

    this should grab out the gold amount, the duration(necessary to parse through it) and the coins for it in groups... you can grab those two relevant groups and do maths with them as is


    note: there's \t and \n\r characters in there, so search-replace them out or adapt the regex to match exactly
    Edited: August 9, 2020

  3. I wrote this some time ago, to be used with a user script helper (like Violentmonkey)

    Code:
    // ==UserScript==
    // @name Gold:Coins ratio column
    // @namespace Violentmonkey Scripts
    // @match https://www.warmane.com/account/trade
    // @grant none
    // ==/UserScript==
    
    "use strict";
    
    (() => $(`<a href="#" style="float: right">Show gold ratio</a>`).insertBefore("#wmstore").click(() => {
      const originalTable = $("#data-table")[0].outerHTML
      const originalScript = $(".jspPane > script:not([src])").html()
      const originalTableHTML = $.parseHTML(originalTable)
    
      $(originalTableHTML).find("[data-index]").removeAttr("data-index")
    
      $(originalTableHTML).find("thead th[aria-label^=Price]").after(`
        <th align="center" class="sorting" tabindex="0" aria-controls="data-table" rowspan="1" colspan="1" style="width: 77px;">G:C</th>
      `)
      $(originalTableHTML).find("tbody tr[role=row]").each(function(idx) {
        const gold = $(this).find("td.name .numeric").html()
        const coins = $(this).find("td.costValues .numeric").html()
        const ratio = gold / coins
        const printable = Math.round(ratio * 100) / 100
        $(this).find("td.costValues").after(`
          <td align="center" data-order="` + ratio + `"><span class="numeric">` + printable + `</span></td>
        `)
      })
    
      const wrapperParent = $("#data-table_wrapper").parent()
      $("#data-table_wrapper").remove()
      wrapperParent.prepend(originalTableHTML)
      wrapperParent.prepend(`<script>` + originalScript + `</script>`)
    }))()
    This is what it looks like. The column header is clickable and can be used to sort the table:

    Edited: August 15, 2020

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •