menu

Insider: Excel for Fun and Profit

Posted on by Matthew Lewis

Are you a Quiet Speculation member?

If not, now is a perfect time to join up! Our powerful tools, breaking-news analysis, and exclusive Discord channel will make sure you stay up to date and ahead of the curve.

Learn More
Sign Up

Collecting data used to be a time-consuming chore. Today weā€™ll go over how to use your computer and Microsoft Excel to do some basic data collection.

You donā€™t have to be a whiz at computer programming to use Excel for this purpose, but it certainly helps to have some technical skills in your pocket. For the purposes of this article, Iā€™m going to assume readers have no computer programming experience, but can use Excel and are keen to learn.

My own background in computer programming is limited to a course in Visual Basic while I was working on my undergraduate degree. Thatā€™s it. However, in my present line of work there are copious amounts of data to sift through. In order to deal with it all in an efficient manner, some shortcuts are definitely handy.

Initial Steps

First of all, youā€™ll have to load up a new workbook in Microsoft Excel. Then add a bunch of sheets and rename them. For each set I am interested in, I use a new worksheet, named using the three-letter code for that set. I also arrange them chronologically.

In this case, I started with SOM, MBS, NPH, M12, ISD, DKA, AVR, M13, RTR, GTC, DGM, M14, THS, and then BNG. All told, thatā€™s 14 sheets. Select all of these sheets together, and then put ā€˜Dateā€™ into cell A1, and ā€˜Novaā€™ into cell B1.

Next, there are three more sheets to add in, which is where the macro will work from. The first is ā€˜Back Endā€™, the second is ā€˜Novaā€™ and the third is ā€˜Nova2ā€™.

Now there should be 17 worksheets total, with the first 14 being each set back to Scars of Mirrodin (SOM). These sheets are where your data will show up, which youā€™ll be able to review over time. The last three sheets are where the macro does its work.

Next, go to the ā€˜Back Endā€™ worksheet which you just added in. This is where we enter some data that the macro will use. Copy and paste the following onto the ā€˜Back Endā€™ worksheet. ā€˜Setā€™ should be in cell A1 with the three-letter set abbreviations beneath it in A2, A3, etc. ā€˜Nova Nameā€™ should be in cell B1 with the set names beneath it.

Set Nova Name
SOM Scars of Mirrodin
MBS Mirrodin Besieged
NPH New Phyrexia
M12 Magic 2012
ISD Innistrad
DKA Dark Ascension
AVR Avacyn Restored
M13 Magic 2013
RTR Return to Ravnica
GTC Gatecrash
DGM Dragon's Maze
M14 Magic 2014
THS Theros
BNG Born of the Gods

 

The macro will be using a simple numbered loop to go through each of the sets in turn. This is an unsophisticated approach to keep track of everything, but it works for me!

The Macro

After the workbook is properly set up, we can then copy-and-paste in the macro. Below is a computer program I have written made up of one main chunk of code, and then four other smaller chunks of code. Itā€™s a good practice when writing a computer program to make simple, flexible bits of code. Trying to jam everything together in one long piece of code makes it much harder to debug and update.

Also, when writing macros for Excel, words and symbols that appear after a single quote will be ignored. These are called comments and are used by good programmers to help make a program understandable. For more complex programs, itā€™s also useful to remind yourself what you were trying to do!

Copy and paste the chunk of code found below into the macro editor in Excel.

Sub Start()

'this is the main part of the macro
'all the other functions are called from here

Application.ScreenUpdating = False

Dim SetName As String
Dim i As Integer
Dim LastRow As Long

i = 2

Do
Sheets("Back End").Select
SetName = Range("A" & i).Value
Sheets(SetName).Select
LastRow = (ActiveSheet.UsedRange.Rows.Count + 1)

With Range("A" & LastRow)
.Value = Date
.NumberFormat = "mm/dd/yy"
End With

i = i + 1

Loop Until i = 16

Call GetNova

Call SearchNova

Call LoadNova

End Sub

Public Function GetNova()

'this function loads the supernova prices into the Nova sheet in Excel

ActiveWorkbook.Sheets("Nova").Select
Cells.Select
Selection.Delete Shift:=xlUp

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.supernovabots.com/prices_0.txt", Destination:= _
Range("$A$1"))
.Name = "set"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Function

Public Function LoadNova()

'This function loads the set prices into Nova2

Dim SetName, NovaSetName, Price As String
Dim EndPriceInt, RowNumber, NovaPrice As Integer
Dim LineNo As Range

i = 2

Do

ActiveWorkbook.Sheets("Back End").Select
NovaSetName = Range("B" & i).Value
Sheets("Nova2").Select
EndPriceInt = InStr(1, ActiveSheet.Range("A:A").Find(NovaSetName), "]")
Columns("A").Find(NovaSetName).Select

RowNumber = ActiveCell.Row
Price = Left(Range("A" & RowNumber).Value, EndPriceInt)
Price = Right(Price, 4)
Price = OnlyDigits(Price)
NovaPrice = CInt(Price)

Sheets("Back End").Select
SetName = Range("A" & i).Value
Sheets(SetName).Select
Range("B" & ActiveSheet.UsedRange.Rows.Count).Value = NovaPrice

i = i + 1

Loop Until i = 16

Sheets("Nova2").Select
Cells.ClearContents

End Function

Public Function SearchNova()

'this function goes through the web data and finds the
'entries with set prices

Dim i, j As Integer

j = 2
ActiveWorkbook.Sheets("Nova").Select
For i = 2 To ActiveSheet.UsedRange.Rows.Count
If InStr(1, Range("A" & i), "=") = 1 Then
Range("A" & i).EntireRow.Copy Sheets("Nova2").Range("A" & j)
i = i + 1
j = j + 1
End If

Next i

'the next is to delete Mirrodin Pure vs New Phyrexia, it screws up looking up NPH's price
'note, as new sets are added, need to adjust this

Sheets("Nova2").Select
Rows("23:23").Select
Selection.Delete Shift:=xlUp

End Function

Public Function OnlyDigits(s As String) As String

Dim retval As String ' This is the return string.
Dim k As Integer ' Counter for character position.

' Initialise return string to empty
retval = ""

' For every character in input string, copy digits to
' return string.
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next

' Then return thestring.
OnlyDigits = retval
End Function

After cutting and pasting the chunk of code into the Excel macro editor, be sure to save it. I called my macro 'Start'. When you are ready to run the macro, click on the Macros button in the tool bar, then View Macros, then select ā€˜Startā€™ in the window that opens and click ā€˜Runā€™.

The macro will now do its work. After about 30 seconds, it should be done. Now you can click on each of the worksheets and see the data you have collected.

I try to use this macro on a daily basis in order to track data that I can look at and analyze on my own. It should be quite easy to set up, but I recommend working through it to try and understand what is happening in each block of computer code. The code that I have provided here could easily be adapted to track booster prices, for example.

Matthew Lewis

Matt Lewis currently lives in Ottawa, Canada and is a long time player and PTQ grinder who now speculates and plays exclusively on MTGO. He's always ready to discuss ideas and investment strategies, so drop him a line in the comments, the forums or on modo, username mattlewis.

View More By Matthew Lewis

Posted in Finance, Free Insider, MTGO

Have you joined the Quiet Speculation Discord?

If you haven't, you're leaving value on the table! Join our community of experts, enthusiasts, entertainers, and educators and enjoy exclusive podcasts, questions asked and answered, trades, sales, and everything else Discord has to offer.

Join the QS Discord
Open Discord

Want to create content with Quiet Speculation?

All you need to succeed is a passion for Magic: The Gathering, and the ability to write coherently. Share your knowledge of MTG and how you leverage it to win games, get value from your cards ā€“ or even turn a profit.

Apply Today!

38 thoughts on “Insider: Excel for Fun and Profit”

  2. If anyone is having trouble getting the macro working on their computer, send me your email address in the forums and I will email a copy of an Excel workbook that has the working macro.

    Cheers,

    Matt

  3. I know there are multiple ways to solve this issue but I don’t have excel at home.

    -I could write a program to import it and export to html or something
    -A google doc could be nice if shared between QS insiders
    -Someone hosts the import on their site
    -Trader tools?

    Anyways, cool stuff. Let me know if you need coding help.

  5. I’m working on Java code that pulls data from multiple sources for paper magic for on the fly analysis during trading, ultimately aiming for an Android app. Is this what you are building towards?

  15. We have a discussion in the forums about tracking profitability. I am getting a subscript error on the “Sheets(SetName).Select” line, so I’d love to see the functioning spreadsheet to compare against/merge into my own. I am looking to brew up an application this weekend to do something similar, possibly with adding customized web scrapers for searching prices on those non-crystal commerce secret sites.

    Please send a copy if you would to mrmacabreman@yahoo.com

  16. Just getting around to reading this article and wanted to check before going through it; will this work with the recent (3 days ago) change to the supernova bots site?

    Cheers,

    Jimmi

      2. Hmm.. having some issues, was wondering if you’d be able to send the sheet to jarhed323@gmail.com?

        Keep getting “Object Doesn’t support this property or Method”, which refers to the following line of code: “.PreserveFormatting = True”. Is this because I’m using a Mac version of Excel (2011)?

        Cheers,
        Jimmi

        1. Found the error, it was in the way that Excel accepted the ‘ in “Dragon’s Maze”. Replaced with correct ‘ and all is well!

          Cheers,

          Jimmi

  19. Hi Matthew,

    Great information, I used to do a lot of work with Excel and Access; however, after replacing pc’s haven’t repurchased. Am curious to see if I can update the code to work with the free “similiar” program. When you have the time, would you please send me a copy of your workbook? rlhemmes@gmail.com

    Thank you very much, in advance šŸ™‚

    Rhonda

  22. I changed the apostrophe in Dragon’s Maze but I’m still getting an “Object doesn’t support this property or method.” Error and won’t run. It seems to be the GetNova function lines .RefreshPeriod=0 to .Refresh BackgroundQuery:=False. But I don’t know how to fix this, if anyone has any ideas.

Join the conversation

Want Prices?

Browse thousands of prices with the first and most comprehensive MTG Finance tool around.


Trader Tools lists both buylist and retail prices for every MTG card, going back a decade.

Start Using Trader Tools

Latest Magic Sets

Aetherdrift
Innistrad Remastered
Spotlight Series
Foundations Art Series
Foundations Front Cards
Foundations Jumpstart Front Cards
Foundations
Foundations Commander
Foundations Promos
Foundations Jumpstart
Browse All Sets
Yawgmoth Thran Physician wish Wirewood Symbiote Wild Nacatl Violent Outburst Urza's Saga Through the Breach The One Ring Suncleanser Solitude Slickshot Show-off shuko scion of draco Ruby Medallion Ragavan Nimble Pilferer Psychic Frog Primeval Titan Phlage Titan of Fire's Fury Orcish Bowmasters Omnath Locus of creation

ION Suite

Discover ION Scanner and ION Retail.
Quiet Speculation