Retrieving Grand Exchange data with excel

[pulli23]

Retrieving Grand Exchange data with excel

Using macros to work around the limtations

 

 

Introduction

In this tutorial a way to retrieve data from the grand exchange will be presented and explained. The method uses the visual basic for applications language, commonly known as "macros". The tutorial is aimed at other developers who are searching for a reliable easy method of incorporating this data into excel sheets. Prerequisites for this tutorial are knowledge of the basic excel commands, but no knowledge of VBA is expected nor needed - the tutorial won't explain any of the VBA or EXCEL commands and is only focussed on how to use the macro. Excel 2007 or 2010 is recommended, though previous versions work as well: the only version which won't work are the "starter" version as they have macroes often disabled.

In this post first will be discussed why a macro is the best option, a quick review of other options and the disadvantages. After that an thorough explanation follows on how to build your own sheet with these macros. During that chapter I will also explain the internal workings of the macro and the safety will be quickly discussed. This will be followed by a paragraph discussing the limitations & problems of the presented method. Finally the tutorial will be concluded with an example, and a quick summary of the usage.

 

 

 

Contents

Introduction
Contents
1) Why a macro?
2) How to use the macro
    2.1) Adding a macro to your workbook
    2.2) Using the GE macro
    2.3) Safety of the GE macro
3) Limitations
4) An example
Summary
Final notes

 

 

 

1) Why a macro?

Why would anyone use a macro? Macros bring all kind of difficult side effects (can't save as a normal file anymore, always a security warning), and are possible safety leaks? While that is true, there are simply things which can't be achieved without using macros, and on top of that macros also allow for neater user inteface. Thus macros in short will make it much easier for the users while slightly increasing the difficulty for the developer.

 

In the case of the Grand Exchange there are a few methods to retrieve data without using macros. (Though when looking in the how those methods work we see they aren't so difficult from our macro). The first and most commonly used method is to use standard excel interface: in the data ribbon under "get external data" one could click on "from webpage". Then in the pop-up screen one should navigate to the grand exchange and search for the item. Then one clicks on the correct table (for the grand exchange there is sadly only 1 table: the whole webpage) and import it. While this works it has quite a few drawbacks, which macroes don't have:

First of all fore each item alone you have to go through the grand exchange manually and update the items, this quickly becomes a hassle and getting the values manually becomes easier.

Secondly to update the page, you have to find the original cell where the table was started and right click there to "refresh". Especially if you're not the original creator this is very troublesome. (and again for each item this has to be done in a row.

thirdly the items come in "raw" format, meaning there is a lot of extra data (see pic %%%) which is redundant and filling up the worksheet. What's worse is that you can't reliable remove this data as it would return as soon as the query is refreshed.

fourthly there are a few limitations on the grand exchange which will prevent you from entering more than 12 terms in a reliable matter. This means you have to build for each 12 items a new query. Also if the items are spread over multiple pages in the grand exchange it's very difficult to miss it.

So to conclude while this method works "on paper' there are many limitations prevent it from being useful in bigger projects where there are more than 3-4 items.

 

Another method would be to parse the data with a separate program, or PHP file, after which it is loaded by the "get external data" interface. By parsing the data into a better format for excel one could prevent the problems from above. However there are some other problems rising then:

First of all, this makes your workbook depending very much on an external program. If the program would be included with the package you're asking a lot from the user (he has to run the updater manually), and it isn't 1 neat file anymore. If the program would be stored online and the excel sheet become very dependent of other sources.. and might at a some point no longer function.

To conclude, this options works very well, however it is demanding on the user.

 

Other options such as using google docs etc might also exist, however there will always be the difficulty of important stuff into the excel sheet, and getting the data at the right positions. Using macros this is all streamlined, and in the presented macro there is a work around presented for each problem with the grand exchange.

 

 

 

2)How to use a macro?

In this chapter we will first discus how to generally add macros to a workbook, after that the GE macro will be descussed, first explaining how to use it in your workbook, then explaining quickly the internal working of the macro. The chapter will conclude with an overview of possible problems with macroes and how to tackle those.

 

 

2.1)Adding a macro to your workbook

First of all we have to know how to open the macro editor. There are several ways to open the editor. The easiest way is to simply press "ALT+F11". Another option is to go to the developer ribbon, and press the first item there (named "visual basic"). On how to enable the developer ribbon, MSDN has an excellent article on how to start writing a macro.

Once you open the macro editor, there should be a screen looking such as in pic %%%. It consist of 3 child windows: the window labeled 1 is the "project explorer", here in a treeview you can see all items currently in your excel file - I will return to it later. The window labeled "2" is the main window, this window will display the code of the selected item. The window labeled 3 is the properties window, it can be ignored for now.

Now that you opened your macro editor, you should get the macro I build:

It can be downloaded:

(alternative mirror HERE, or if one prefers to build the macro himself, the source is Pastebin)

Though I prefer if you could download from the first source, as that allows me to track the number of downloads making me maybe more eager to update the macro :P.

Once you downloaded the "bas" file you can important it into your project.

To do so click on the "file" menu and then "import file": browse to the location the file is saved and import it.

In the tree view, under your project you will see a new folder: "modules", this folder will hold all your macros. Open the folder and you should find a file "GetGEPrices", double click to open and in the main window the file should now be visible (a hell load of code, we'll get to that later).

 

Now that you have done there are a few things to remember:

-First of all you can't save as a standard excel 2007-2010 file (.xlsx) anymore, you have to use the macro enabled file (.xlsm). This is not a problem in pre 2007 version of excel though

-Secondly every time a file is opened with macros enabled, they are disabled by default for security measures. You have to enable the macros by clicking on the bar. (in the developer tab you can also change the default behavior, but I don't recommend doing that)

 

If you understand and have done this, you're good to go, and you can actually start using the build macro.

 

 

2.2) Using the GE macro

Now that you know how to add a macro to your workbook it's time to let the macro do the annoying stuff for you. This macro will retrieve GE prices of items you put in a vertical list. (and will return the names, price and change today)

The macro is completely controlled by setting 13 variables, the "default" lines are shown below . It shouldn't be hard to find those lines in the macro (they are the second "block" of code). I will explain each line below

    Set DataSheet = Sheets("Sheet2")
   Set InputSheet = Sheets("Sheet1")
   InputRow = 2
   OutputRow = 2
   OutputNameCol = 1
   OutputPriceCol = 2
   OutputChangeCol = 3
   InputNameCol = 1
   RemoveTrailingRows = True
   OnlyExact = True
   OrderAsInput = False
   showdebug = False

 

Set DataSheet = Sheets("Sheet2")

This lines sets the sheet in which the data should be outputted.

Simply replace Sheet2 with the name of your desired sheet, don't forget the double quotation marks

The data sheet can be the same as the input sheet (though make sure you don't overwrite the input-column)

 

 

Set InputSheet = Sheets("Sheet1")

This lines sets the sheet from which the data should be read.

Simply replace Sheet1 with the name of your desired sheet, don't forget the double quotation marks

The input sheet can be the same as the data sheet (though make sure you don't overwrite the input-column)

 

InputRow = 2

The row number from which to start reading the data list (top row has number "1")

Replace the number [i[2[/i] with the desired number. The number should be greater than 0

 

OutputRow = 2

The row number from which to start displaying all data (top row has number "1")

Replace the number [i[2[/i] with the desired number. The number should be greater than 0

Note that if "OrderAsInput" is set to true this variable is ignored.

 

OutputNameCol = 1

The column number where the found "names" of all items will be displayed. (The first column, labeled "A" has number 1)

Replace the number [i[1[/i] with the desired number.

Using 0 or a negative value makes the script not display this data

 

OutputPriceCol = 2

The column number where the found "prices" of all items will be displayed. (The first column, labeled "A" has number 1)

Replace the number [i[2[/i] with the desired number.

Using 0 or a negative value makes the script not display this data.

 

OutputChangeCol = 3

The column number where the found "price change" of all items will be displayed. (The first column, labeled "A" has number 1)

Replace the number [i[3[/i] with the desired number.

Using 0 or a negative value makes the script not display this data.

 

InputNameCol = 1

The column number where all the items to search for should be presented (The first column, labeled "A" has number 1)

Replace the number [i[3[/i] with the desired number.

Using 0 or a negative value makes the script not display this data.

 

RemoveTrailingRows = True

Sometimes when changing the items there will be less items than the previous call. If set to true the script will remove all extra items from previous calls to the function.

Replace True with either True or False

Note that if "OrderAsInput" is set to true this variable is ignored.

 

OnlyExact = True

If one for example searches for "evil turnip" 7 items show up. If this variable is set to False all these items would be present in the sheet. Setting it to "true" makes only exact matches present.

Replace True with either True or False

Note that if "OrderAsInput" is set to true this variable is considered "True".

 

OrderAsInput = False

The name is slightly misleading, if set to False for each item it will search the exact row of the input. And then place the information of the searched item on this line. This is particularly useful if you want to get the outputted data next to the input.

Replace True with either True or False

Remember that you should never overwrite the input column.

 

showdebug = False

If set to true it will show some debug information. - There will be an extra sheet with all raw data from the pages loaded, the 3rd row in that sheet will contain the actual urls for each page. Also at the currently selected cells (and below) it will put the strings it used to search. Better keep it false, unless you actually have a bug and are trying to solve it yourself.

Replace True with either True or False

 

These variables are hopefully self explanatory and you should just follow your own intelligence mostly :), a quick setup if you want for example to get the output prices next to the items:

    Set DataSheet = Sheets("sheet1")
   Set InputSheet = Sheets("sheet1")
   InputRow = 2
   OutputRow = 2
   OutputNameCol = -1 'We're using the names of the input row
   OutputPriceCol =  2
   OutputChangeCol = -1 'not interested in the change
   InputNameCol = 1
   RemoveTrailingRows = True
   OnlyExact = True
   OrderAsInput = True
   showdebug = False

 

activating he macro

Now that you setted up how the macro works you still have to active it's use.

A simple way that doesn't require the developer to do anything is to go each time to open the ribbon "view", then click on the "macro" item "view macros", select the wanted (GetGEPrices) macro and press "run". That was a lot of work to activate it, and the tutorial started to say that marcos allow for a neat user interface and are easy for the user of your workbook.

The other simple way is using a textbox. This textbox would act as a "button". To create such a button-textbox, first add a textbox to your workbook at the desired location (insert->textbox), give it some neat layout (I'll leave that for your imagination, hint: use bevel & emboss).. And hen right-mouse click your textbox, and from the drop down menu select "Assign macro...". In the pop-up screen search for the wanted (GetGEPrices) macro and press "OK". Now if you click on that textbox with a single left click the macro is activated!

Congratulations!

 

Now you're probably wondering what it actually does? Well first of all it reads the input (in batches of 12), it will keep reading the input-column until an empty cell has been found. Then for that input it simply searches the GE and puts the results -removing any format and extracting only useful data- into the output columns. Next chapter will describe in more technical detail.

 

 

2.3) Internal workings & safety

This chapter will explain in a bit more detail how the script actually gets its data, skip it if you don't like to read dull technical information.

The macro works by doing a "get" connection at the runescape site. Since I don't know of anyways to keep a connection open and ask multiple queries with VBA, for each search string it has to open a new connection. There are also a few limitations on the GE which work it's way into the macro. Notably the GE can only handle 12 different terms, so the script reads only 12 items a time. Then it opens the first page with these items, and copies that page to a temporary sheet, then it processes the items in that page line-by-line. It will continue to do this for all pages of the ge. After that it reads the next 12 items.. This cycle continues until the script reads an "empty" input space.

The script has a special sub-function to handle numbers and the special character (K, M & B) so the final cells will always have a number in them.

 

The biggest safety problem with scripts are left-overs when an error occurs. Looking at the places where errors might occur the only obvious point is during the collection of the webpage (if ie your internet connection fails). However this possible error is being catched: if internet fails it will simply try to open the next page. (until there are no more pages & items to search for and it will end). This won't result in any leftovers.

However if at any point something crashes it still shouldn't be a problem. There simply is only 1 (visible) extra sheet which should then be removed manually.

 

 

3) Limitations

There are a few limitations to the macro, some which are important some which are not.

-The input (and output) has to be in "column" format.. This was simply easiest to program and most logical (if there is strong demand I'll make a way to change it to rows using another variable)

-The input has to end with an empty cell.

-Special character aren't handled well by the GE. Notably & and + aren't handled well by the GE, I made a workaround by replacing the character by * (this is all done in the script though, no need to change anything in the sheet). It might make the script more slowly as much more results will be found than necessary.

 

 

 

4) An example

A complete example can be found:

 

 

summary

To summarize these steps do you have to do to create a GE-listening workbook:

• Download the macro (HERE, or HERE)
  
• Create a new workbook with macros enabled (save as .xlsm)
  
• Import the macro
  
• Set all variables
  
• Add a textbox-button to activate the macro

Simple isn't it? :P

 

 

 

Final notes

This spot is open for some discussion & important notes made by other members from tip.it. Also I'll keep a to-do list for now

-I still have to add pictures

-Second (rapidshare) mirror has to be uploaded

-Example should be explained

 

 

I hope you can take your advantage with this, and I hope to see spreadsheets have automatic GE-updating prices in the future.

[Ronan]

Lovely! :) It's nice to see an alternative to the standard method of retrieving data used - I can't say I'm that experienced with VBA but it looks like a nice approach.

 

I've not tested out any of the code yet, but read through the article - I'll have to try my hands on VBA and see how it all works though. Code also looks nice and neat - always a help. :)

 

There were a few mistakes in the article - just small things, but I've put them here for you either way.

 

 

 

GE macro will be descussed, first explaining how to use it in

possible problems with macroes

methods work we see they aren't so difficult from our macro

This lines sets the sheet in

Replace the number [i[3[/i] with the desired number.

activating he macro

you setted up how - ("you have set up")

(K, M & B) - (Lovely interfering smileys...)

script more slowly - ("script slower")

these steps do you have to do - ("the steps you have to do")

 

 

 

And as you said, the mirrors aren't uploaded yet.

 

Nicely written up article, I'll be sure to message back once I've played around with the code. :)