Thursday, April 18, 2013

Percentage Corrections & Estimating Potential Profits

Hello friends,

I'd like to take a quick moment to say "Thank You" to everybody that helped with the Personal Percentages spreadsheet. I've had a chance to catch up on a few things and perform some much needed updates. There is also an additional tab called "ProfitLossCalc", which I'm sure many will all find useful when looking to quickly assess a profit/loss.

New Features:

  • Updated drop-down menus to match updated percentages inside of MTGOLibrary
  • The ability to preview card costs and calculate potential profits with/out fees
    • based on type of license (Pro = 0%, Wiki-Ads = 2.5%, and No Wiki-Ads = 3.5%)
    • There is no longer an "N/A" or a "Value/?" in fields where the formula can not calculate a number; instead they will appear blank and update as you enter data
  • Ability to calculate profit or loss of trades while considering, if any, above mentioned trade fees
Now, let me say that if you are already familiar with the sheet and just want to get started, you can download the file here.  If you're interested in what's new (aside from above bullets) and/or this will be your first time using the sheet, read on.

Updated Drop-down menus

With the recent updates there have been a lot of great new additions to MTGOLibrary. One such addition, is the ability to select percentage corrections in increments of 1. This makes it easier than ever to set prices for your bot. You can now select increments starting as low as 1 inside of the PrecentageCalculations.xlsm

To Fee or Not to Fee...

There are 2 different modes that MTGOLibrary bots operate in, Pro and Lite. Beneath each of these modes, there are 2 ways one can run a bot. Either by paying a percentage of all transactions or by buying a subscription.

While the 2 operating modes are quite different, the methods of paying for their use are the same. The first way is by purchasing credit. This method of payment is done via a "licensing fee" which is based on a percentage of all your transactions. In this later method, for each transaction the licensing fee is deducted from your purchased credit. This is the method most smaller bots and low transaction bots will choose to run in, making this tab particularly important for them.

Now, depending on whether or not you choose to have the Wiki message displayed to your users at the beginning of a trade, will determine what your licensing rate is. Those that choose not to display the message pay a licensing fee of 3.5% on all transactions. Those folks that choose to display the message pay a licensing fee of 2.5%.

If you're interested in seeing how much a card will truly cost you (the cost in tickets + the licensing fee), you can now view that from the "prices" tab. Everything is as before when it comes to entering the information, except there are 3 more columns on the end. These display your profit with no fee, your profit with a 2.5% fee and your profit with a 3.5% fee.



In an effort to help determine how much one made on the sale for a particular card, there is the "ProfitLossCalc" tab. This tab will take buy/sell price, license percent and a quantity. Given this information, it will calculate if you made a profit or lost money (as seen in below screenshot).

For example, if I enter a price of 28.758 with a license fee of 2.5% and buy a quantity of 3, it will really end up costing me 88.431 total. When I sell those 3 cards at 31 each (with same 2.5%) I will bring in 90.675, a profit of 2.244. 


Care to see how much you could make if you had a subscription to the pro version of your bot? No problem, go ahead and just delete the 2.5% in each of the Buy and Sell Fee boxes. There you go.

Interested in getting started with the new sheet yet? Great! You can download the latest version here.
As always, I'll look forward to your feedback. Email, MTGO, Google Chat, Skype...whatever works for you I'm on them all :)
Cheers,

Jimmi
MTGO: b1nary0mega
The 'b1naryb0t' network

17 comments:

  1. Replies
    1. LOL...I'm not sure about that, but Thank You, Albert!

      Delete
  2. nice new additions james. Ill be having a look and give u positive criticism soon.

    ReplyDelete
    Replies
    1. Thanks, Carlos. I'll be looking forward to it.

      Delete
  3. Just a quick question for you guys: are there tools to convert a xls/xlsm to executable? In fact it would be nice to include James' tool, compiled as executable, directly in the ML Bot installer, so everyone will benefit (even the ones that do not read the blog and thus are not aware)

    ReplyDelete
    Replies
    1. Hi Albert,
      There are several converters out there. I've had zero experience with them, so I would be unable to make a recommendation on one.

      I'm working with Sebastian and ultimately, this will be included with the MTGO Tool. The links to that and a bit about it are located here.

      As soon as I get a standalone version of this programmed (may be a week or 2), I'll get an exe file out to you.

      Cheers,
      Jimmi

      Delete
    2. Hi James,

      yes please, a standalone version will be wonderful. Not everyone has Excel, because you are supposed to pay for it.... ok ok, many crack it, but many don't :-)

      Delete
    3. Im sure you wouldnt like to see a "MTGOLibrary crack" on piratebay either albert heehe.

      Delete
  4. google "Excel to exe converter". ;)

    ReplyDelete
  5. great tool indeed :) just a question: will xlsm macros work properly in OpenOffice Calc?

    ReplyDelete
    Replies
    1. Hi Antonio,
      For the most part, OpenOffice Calc does pretty well with excel. The normal mathematics and standard cell formulas should work. The macros ("Process Prices" button) will probably not work as they are Excel VBA-based. I will try to test later on today.

      Cheers,
      Jimmi

      Delete
    2. Antonio,

      I use OpenOffice and LibreOffice on my Ubuntu Linux and everything is fine there. Macros does not work but I can still use the spreadsheet.

      Delete
  6. Replies
    1. I'm always open to hearing offers, Pette. My contact info is on the about tab. Glad you like it :)

      Delete
  7. How does this calculate actual profit? Price changes can occur could dramatically increase or decrease margin. Maybe I just don't understand how to use it.

    ReplyDelete
    Replies
    1. doesnt take into account price changes, as this (buying for 13 on day1 and selling for 10 on day 20) can actually result on loss.

      Delete
    2. Hi Teamstoge,

      Profit over the long term is a bit more difficult to calculate, and not truly what this tool was designed to do. For that, I would really recommend the "Card Log" feature of MTGO Library's web interface. If somebody was interested in calculating profit or loss of an entire collection, then I would recommend the method and tools described by Jason in his post here.

      What this tool aims to do is to provide folks a rough idea, based on information they input into the sheet, of whether or not something has the potential to provide a profit or loss based on a static scenario.

      A few examples would be the following:

      1. Somebody is setting up a new server and may only have limited resources. They'd like to offer a bit more for cards and sell for a bit less, hoping to drive more business. Knowing that the margin of profit is going to be small, they need to know how close they can cut it before they are no longer making a profit. So, they could select what they think might be desirable settings in the "settings" tab and then run a few scenarios on different cards in the "prices" tab. They could then take those numbers (keeping in mind this is a static scenario based on current date of the persons most recent CardsMTGO3.txt file) and run them through the "ProfitLossCalc" tab and see how they would end up.

      2. My server often buys higher end cards (such as Falkenrath Aristocrat, which I actually purchased 3 of the other day much to my surprise). I know how much I bought them for so I enter that into the "ProfitLossCalc" tab under the buy price. After putting in the licensing fee that my server pays, I can then begin to assess how much I would need to set inside of a "PersonalPricesXXX.txt" file in order to break even.

      2.5 Instead of placing a static price in a "PersonalPricesXXX.txt" file, I can remove the fee on the sell part and see how much I would need to sell those cards for in order to turn a profit, if I were to take them off the bot and do the footwork myself.

      I hope this helps provide a few ideas of some of the uses for this sheet. These are just 2(.5) examples, but they are taken from my own experience in just starting out (February this year).

      Please, if you have any suggestions about how to make this better, or botting in general, I'd love to hear them. As I mentioned, I'm new to running a bot business and ALL feedback is welcomed feedback.

      Cheers,

      Jimmi

      Delete