Excel & Google Doc Formulas for Auto Updating GE Prices

[YoHoJo]

Can someone post example lines of code for Google docs and excel that grab real time GE prices from runescape GE website.

[NexPB]

I'm not sure what you need but this is php script I made it will get the midprice by item-id.

ex:
http://scripts.master-bot.org/GEPriceChecker.php?id=5316 (= magic seed)




<?php

function getprice($id) {

$url = "http://services.runescape.com/m=itemdb_rs/api/catalogue/detail.json?item=".$id;

$startat = "current";

$stopat = "today";

$ch = curl_init();

$timeout = 5;

curl_setopt ($ch, CURLOPT_URL, $url);

curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);

curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT, $timeout);

$start = curl_exec($ch);

curl_close($ch);

$exploded = explode($startat,$start);

$exploded = explode($stopat,$exploded[1]);

$find = array(",", "trend", "neutral", "{", ":", "price", "}", ",");

$result = str_replace($find, '', nl2br($exploded[0]));

return str_replace('"', '', $result);

}

echo getprice($_GET["id"]);

?>

[YoHoJo]

Hmm that actually helped, thank you very much!!!

Other responses/methods are also helpful too though!

[Abu]

It's better if you do this while you read....

Sign into Google Docs.

In Google Docs click Create -> Spreadsheet

Now in A1, paste the following code:

=ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Coal Ore", "table", )

You can replace 'Coal Ore' with the item you want to search for.

If you did this correctly, you should now have a table with the information of all your search results with their prices.

Now what you want to do is locate the item price you want to find and note down the row and column. In this case I want to find the price of 'Coal Ore' and the price lies in Row 2, Column 3.

With this in mind, replace the following code with this:

=Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Coal Ore", "table", ),2,3)

Can you see what I've done? I've put Index before ImportHtml and two numbers '2 and 3' at the end. This means it will get the information stored in row 2, column 3.

After entering this you should now find it only has the price of Coal Ore displayed.

So what you want to do is hit File -> Share and allow everyone with a link to access this spreadsheet. Get that link and open it, it should look something like this: https://docs.google.com/spreadsheet/ccc?key=0AuXQNkjY2OyVdG8xWHBmbTVaTGduUWtfdVd2anM1a UE

You can now use Internet Functions (http://docs.villavu.com/simba/scriptref/web.html?highlight=internet) and GetText (http://docs.villavu.com/simba/scriptref/ocr.html) functions from the Simba documentation to get the price which is displayed on the spread sheet.

Hope that helped :D


FYI: This took a lot of research as I just learnt this myself, so thanks I guess :p

[YoHoJo]

Google docs is really pissing me off.
How to do this in excel, with auto updating values please.

Google docs get fussy and says errors and crap sometimes.
When using excel and NexBP's site (I click the Data tab in excel, import from web, and put in the URL) it works, but the 'data' takes up more than just one cell (even though the website is all white with just 1 value?) so when I want to enter multiple values near the first cell I picked, it doesn't let me because It's thinking they are being 'used' already to get data even though they are blank.

Can someone please help!

[YoHoJo]

helpme

[NexPB]

I can't realy help you but if I may ask why do you need it in Excl?

[YoHoJo]

I can't realy help you but if I may ask why do you need it in Excl?

Docs is fine, and your method was working too, but (I think) if I accidentally mess up a line, or type in a wrong item name, or screw up the SRL somehow, the docs throws a pop up error at me, and then the cell I was using becomes completely unusable and It was getting my very very frustrated!

[NexPB]

Docs is fine, and your method was working too, but (I think) if I accidentally mess up a line, or type in a wrong item name, or screw up the SRL somehow, the docs throws a pop up error at me, and then the cell I was using becomes completely unusable and It was getting my very very frustrated!
If you just want the price from the GE.


procedure GetPriceGe;
var
url, price, final: String;
begin

Case (Lowercase(Ore)) of
'iron', 'iron ore': oreID := 440;
end;

url := ('http://www.scripts.master-bot.org/GEPriceChecker.php?id=' + IntToStr(oreID));
price := GetPage(url);

if (orePrice = 0) then
begin
final := Trim(price);
if (final = '') then writeln('FAILED TO GET PRICES! RESTART SCRIPT.');
writeln('Price ore: ' + final);
orePrice := StrToInt(final);
end else
Exit;
end;


This is what I used for my miner.

[YoHoJo]

I want to make a spreadsheet though, going to keep tabs on different prices/profit and stuff. Tanks though for your help I know you're really trying/have answered my question, it's just docs being meh :/

[putonajonny]

I think your easiest way would be to use simba to download the prices and save them to a csv:
program new;

{.include SRL/SRL.simba}
{.include SRL/SRL/Misc/Online.simba}

Const
SavePath = 'C:\Simba\Prices\'; //Make sure this ends in "\"

Var
Items : array of Variant;
Names : array of String;
Prices : array of Integer;

Procedure ItemsToGrab;
begin

Items := [563, 'Nature Rune', 7936]; //You can type the ID or the name
Names := ['Law Rune', 'Nature Rune', 'Pure Essence']; //Make sure these two arrays are the same length

end;

Procedure GrabItems;
Var
i : integer;
begin

SetArrayLength(Prices, GetArrayLength(Items));

for i := 0 to high(Items) do
Prices[i] := GetGEPrice(Items[i]);

end;

Procedure SaveItems;
Var
S : String;
i, n : integer;
begin

ForceDirectories(SavePath);
if(FileExists(SavePath + 'Prices.csv'))then
DeleteFile(SavePath + 'Prices.csv');

for i := 0 to high(Prices) do
S := S + Names[i] + ',' + IntToStr(Prices[i]) + ',' + #10;

n := CreateFile(SavePath + 'Prices.csv');
WriteFileString(n, S);
CloseFile(n);

end;

begin

ItemsToGrab;
if(GetArrayLength(Items) <> GetArrayLength(Names))then
begin
srl_Warn('ItemsToGrab', 'Array Lengths are not the same!', warn_AllVersions);
TerminateScript;
end;
GrabItems;
SaveItems;

end.

Then import it into excel:
http://i.imgur.com/nFPkl.png
Navigate to the filepath you specified in the script above:
http://i.imgur.com/WEJIi.png
then follow the pictures:
http://i.imgur.com/yiNhx.png
http://i.imgur.com/a1xiV.png
http://i.imgur.com/ZY8Xr.png
http://i.imgur.com/Mx2dN.png


When You Want To Update The Prices:
http://i.imgur.com/KrFZZ.png
http://i.imgur.com/FeIqV.png

If you add more items into the array in Simba then it will auto add these to excel when you click refresh

A couple more things:
http://i.imgur.com/ps2na.png
http://i.imgur.com/Ug6mI.png



If you wanted to do this with google docs I could whip the same thing up in php so that the docs could access it

[YoHoJo]

Damn thanks for taking the time to do that yourself and even screenshot each step of the way that was very nice of you!

I think easiest thing to do though would just be to have the proper URL and 'grabbing' of the price though wouldn't you think so?

Also what if I don't want all of my values in a column straight down? What I want them in all different cells all over the palace?

I think a simple formula line I can edit per item and put into any cell I want would be best.

[putonajonny]

Damn thanks for taking the time to do that yourself and even screenshot each step of the way that was very nice of you!

I think easiest thing to do though would just be to have the proper URL and 'grabbing' of the price though wouldn't you think so?

Also what if I don't want all of my values in a column straight down? What I want them in all different cells all over the palace?

I think a simple formula line I can edit per item and put into any cell I want would be best.

I think the easiest way in that case is that I'll whip something up in php you just go to Import Data From Web, navigate to: www.mywebsite.com/grabitem.php?item=563 (or whatever item) then press import

[Abu]

I think a simple formula line I can edit per item and put into any cell I want would be best.

The cell you enter the formula in will be the cell the information will be outputted. So if I put the formula in A1, then the info will go in A1, if I put in in C3, then it will be outputted into C3 etc...

So if I put this into D4:

=Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Coal Ore", "table", ),2,3)

It will get the number from the second row, third column, which is the price for 'Coal Ore' and output it into Cell D4.

Alternatively, if you want the name of the item and the price, then you do exactly the same thing however after you get the table onto your spreadsheet, find which cell your item name lies in, then do this:

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=", B3), "table", ),2,3)

What I did was added Concatenate and instead of putting what I was searching for after '.ws?query=', I put the cell number that my item name was in.

So now it will display the item name followed by the price.

[putonajonny]

Try this, just whipped it up:
http://putonajonnyscripts.hostoi.com/price.php?Item=563
Just enter the item ID/Name after Item= (563 is law rune)

[CephaXz]

Try this, just whipped it up:
http://putonajonnyscripts.hostoi.com/price.php?Item=563
Just enter the item ID/Name after Item= (563 is law rune)

Very helpful thread here! I wanted to learn how to make a progress report with profit/hour report, not using constants to input the profit everytime I start script.

Let's say I want to count the profit of tanning hides. How should I make it so it counts the profit made per item/inventory? Like leather price - hide price.
red dhide leather:
http://scriptauthentication.net16.net/price.php?Item=2507

red dragonhide:
http://scriptauthentication.net16.net/price.php?Item=1749

[putonajonny]

Very helpful thread here! I wanted to learn how to make a progress report with profit/hour report, not using constants to input the profit everytime I start script.

Let's say I want to count the profit of tanning hides. How should I make it so it counts the profit made per item/inventory? Like leather price - hide price.
red dhide leather:
http://scriptauthentication.net16.net/price.php?Item=2507

red dragonhide:
http://scriptauthentication.net16.net/price.php?Item=1749

Add this near the top of your script (at the bottom of the other ones that look like it):
{.include SRL/SRL/Misc/Online.simba}
then add this as a global variable:
Var
ProfitPerItem : integer;
Then during the setup of the script add:
ProfitPerItem := GetGEPrice(2507) - GetGEPrice(1749);
Then a proggy could be:
WriteLn('Profit Made: '+IntToStr(ProfitPerItem * HidesTanned));

[CephaXz]

Add this near the top of your script (at the bottom of the other ones that look like it):
{.include SRL/SRL/Misc/Online.simba}
then add this as a global variable:
Var
ProfitPerItem : integer;
Then during the setup of the script add:
ProfitPerItem := GetGEPrice(2507) - GetGEPrice(1749);
Then a proggy could be:
WriteLn('Profit Made: '+IntToStr(ProfitPerItem * HidesTanned));

Thanks! Finally added in profit made and profit per hour. Didn't know there's such include in simba.

[Abu]

Try this, just whipped it up:
http://putonajonnyscripts.hostoi.com/price.php?Item=563
Just enter the item ID/Name after Item= (563 is law rune)

Can I ask how you got it to output just that number and not the whole spreadsheet format like this?: https://docs.google.com/spreadsheet/ccc?key=0AuXQNkjY2OyVdG8xWHBmbTVaTGduUWtfdVd2anM1a UE#gid=0

[putonajonny]

I have made a new website so that you don't get that free web host thing in excel, try this one: http://pricegrabbing.host-ed.me/price.php?Item=563



Can I ask how you got it to output just that number and not the whole spreadsheet format like this?: https://docs.google.com/spreadsheet/ccc?key=0AuXQNkjY2OyVdG8xWHBmbTVaTGduUWtfdVd2anM1a UE#gid=0

I used php to trim down the string to just the price, then printed it

How to:
http://i.imgur.com/EWtaR.png
Put the URL in like this:
http://pricegrabbing.host-ed.me/price.php?Item=ItemName
or
http://pricegrabbing.host-ed.me/price.php?Item=ItemID
http://i.imgur.com/7yeFX.png

http://i.imgur.com/vv9Rh.png
http://i.imgur.com/RWO50.png
http://i.imgur.com/bbsj1.png
http://i.imgur.com/UhXGT.png

Or in google docs:
http://i.imgur.com/tVDVO.png

[cause]

Putonajonny, that is so helpful, thanks!

[putonajonny]

Putonajonny, that is so helpful, thanks!

Worth making a tutorial?

[Abu]

Worth making a tutorial?

Yarp

Definitely get you some decent rep :p

[Spiker]

Probably a little tedious, but if you still are looking for a way to get GE prices, etc. You can review what I did: http://villavu.com/forum/showthread.php?p=1022981#post1022981

Good Luck