Posts tagged Guide
How to Make Spreadsheets For Runescape Using Google Docs.
Jun 9th
Contents
- 1.0 [ Introduction ]
- 2.0 [ Getting Started ]
- 3.0 [ Using ImportHTML to import GE Prices ]
- 4.0 [ Extracting One Item at a Time ]
- 5.0 [ Using the GE Prices to Make a Spreadsheet ]
- 6.0 [ Concatenate - Another Useful Function ]
- 7.0 [ Dealing with Ks and Ms ]
- 8.0 [ Final Words and Credits ]
1.0 [ Introduction ]
Many of my charming readers have been asking me how I made the spreadsheets that I’ve embedded onto some of my posts. These spreadsheets automatically extract the prices of items from the Grand Exchange Database and then do certain calculations with those prices. The Process is really not too difficult but it can be somewhat frustrating at first if you’re not familiar with how spreadsheet programs work. Hopefully this guide will make it as easy for you as possible.
If you’re not exactly sure what I mean by these spreadsheets, check out the ones I have on the following posts:
1) Beginner’s Guide to GE Merchanting.
2) How to Make Money by Swapping Pouches for Shards.
3) How to Make Money by Making Pouches into Scrolls
You might also want to check out Grimy Bunyip’s Spreadsheets. Grimy is a pro at making Runescape spreadsheets on Google docs. I often use his spreadsheets when looking for the best way to train a particular skill. For instance, by looking at his spreadsheets I found that making air battlestaffs is a great way to train Crafting and cleaning grimy herbs is a great (and profitable) way to train Herblore.
How to Turn this into something beautiful.
======================
2.0 [ Getting Started ]
Start off by doing the following:
2A) Head to Google Docs and sign in. If you don’t have a Gmail account, you will see a button somewhere on the bottom of the screen that will lead you to a page where you can create an account. Once logged in, on the top left part of the page press ‘CREATE NEW’ and then ‘SPREADSHEET’ (see screenshot below).
Make an account and then create a new spreadsheet.
======================
3.0 [ Using ImportHTML to Import GE prices]
After you’ve gotten that all straightened out, it’s time to learn how to use Google Doc’s function ImportHTML which, unavailable in Microsoft Excel and OpenOffice Calc, is the key to extracting prices from the Grand Exchange Database:
3A) The basic ImportHTML code we’ll be using for this guide goes like this:
=ImportHTML("LINK HERE", "table", 2)
The “table”, 2 part is necessary because otherwise it would extract everything on the page rather than only the items and their prices. Go to the Grand Exchange Database and search for an item. Copy the link and insert it into the code where it says “LINK HERE”. Put the entire code, then, into one of the cells on your Google Doc spreadsheet (like A1 or A2). Repeat this until you have imported all the items you need for your spreadsheet.
>> Let’s trying searching “Dragonhide” in order to make a spreadsheet for my Tanning Dragonhides Guide. Notice that other items come up besides dragonhides in the search. That’s okay (we’ll learn how to deal with that more later on) but, for now, just to narrow down the items a bit, I’ll put the price range between 1-5K. The final link, which I will insert into A2, is http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide& price=1000-5000 and, when added to the code, becomes
=ImportHTML("http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide&price=1000-5000", "table", 2)
You now have a list of items that will be updated each time the GE updates.
======================
4.0 [ Extracting One Item at a Time ]
By importing items the way mentioned above, you’ll almost always end up with some extra unwanted items (like Black D’hide Vambraces or Black Spiky Vambraces above). It’s easy to get away with this problem by simply right-clicking the unwanted-item column and pressing ‘hide’ or simply not publishing that sheet but you can also just import items 1-by-1. Whether or not you do it this way is up to you (I personally don’t usually bother) but here’s how to import items 1-by-1, anyway:
4A) Add Index( before the code and , ITEM COLUMN NUMBER HERE) after the code. It looks like this in the end:
=Index(ImportHTML("LINK HERE", "table", 2), ITEM COLUMN NUMBER HERE)
Fill in the LINK HERE and the ITEM COLUMN NUMBER (explained below) and copy and paste into a cell. Repeat this for each item until you have all the items you need.
Importing Items one-by-one
4B) To figure out the column number for, say, Red Dragonhides just count down from the first column until you reach Red Dragonhides. If, like before, you search ‘Dragonhide’ and narrow down the options to prices between 1-5k, Red Dragonhides will be in the fifth column (see image below) so replace ITEM COLUMN NUMBER HERE with 5. The link is, of course, figured out the same way as previously – in this case, it’s http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide&price=1000-5000. The final code, then (see above screenshot), is:
=Index(ImportHTML("http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide&price=1000-5000", "table", 2), 5)
Start counting at the ‘Item – Current Price – Change Today (gp)’ Column
======================
5.0 [ Using the GE Prices to make a Spreadsheet ]
Using the prices you’ve imported to make a worthwhile Runescape spreadsheet can be very easy or very difficult, depending on what you want it to do. A spreadsheet determining how much money you make per Dragonhide per hour is pretty simple and basic, so we’ll start with that and then later I’ll show you some more complex stuff.
5A) First off, import all dragonhides, tanned and untanned using the ImportHtml function (however you choose to do it: 1-by-1 or altogether) and then create a new sheet by pressing the ‘+’ on the bottom right of the same spreadsheet page.
Have your calculations on a separate page from the one where you imported the prices
5B) The new sheet should tell us four things: (1) Item name. (2) Profit per hide. (3) Profit per trip. (4) Profit per hour. Write these four things at the top of each column (i.e. type ‘Item Name’ in A1, ‘Profit Per Hide’ in B1 and so forth). Then simply write the names of the dragonhides in four different cells below the ‘Item Name’ column or, rather than writing it out, you could also just put CELL=CELL WHICH SAYS DRAGONHIDE NAME HERE. It’s really very simple: just click the cell you want, type in =, and then find and choose the cell whose contents you want it to mirror.
On the other sheet “Calculations” on A2 it will now say Green Dragonhide
However you decide to do it, just make sure it ends up looking something like this.
5C) To figure out the ‘Profit per hide’ all you have to do is subtract the price of the untanned dragon hide + the cost of tanning the dragonhide (20gp) from the price of the corresponding tanned dragonhide. The code in the cell to the right of each dragonhide name should end up looking something like this, then: =CELL THAT CONTAINS PRICE OF TANNED DRAGONHIDE-CELL THAT CONTAINS PRICE OF UNTANNED DRAGONHIDE-20.
Notice that there are some rows of cells you can’t see. This is because I hid them.
It should end up looking like this on the other sheet.
5D) To figure out the profit per trip just multiply 27 (the amount you can do per trip) by the cell that shows the profit per hide i.e. C2=27*B2. Because the formula is the same for all four hides, you can simply copy the first cell and paste it into the other three.
Don’t forget that you can just copy and paste
5E) To figure out the ‘Profit Per Hour’ simply multiply the corresponding ‘Profit Per Trip’ by the number of trips you can do in an hour – which in this case is 110 i.e. D2=110*C2.
Okay, that’s almost everything! The only thing that’s missing is a little color.
5F) It’s very easy (and worthwhile) in Google Docs to add coloring and change font type and size. Simply scroll over the cells you want to color and choose the text type, text color and background color for all of them.
Here’s how I colored mine. Ain’t it purty? How will you color yours?
5G) Now you just need to share your spreadsheet with the world. Click the ‘Share’ button in the near-top right corner and share it whatever way you want. You can even embed a spreadsheet on your website or blog like I’ve done and it’ll give your readers a good reason to come back every day. Make sure to post a link to your spreadsheet(s) in a comment on this post, too – I’d love to see it!
Be generous and you will be broccoli. Help others and they will eat you….
======================
6.0 [ Concatenate - Another Useful Function ]
6A) The basic concatenate function code is this:
=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",A2), "table", 2),2,3)
Looks complicated, right? All you need to know, though, is that, with this code, whatever you put in A2 will be put in place of where it now says A2 in the code. So if you type ‘Green Dragonhide’ into A2 the code will now be
=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",Green Dragonhide), "table", 2),2,3)
The new code will tell you what the price of Green Dragonhides are.
Yo-ho-ho it’s magic, you know! Never believe it’s not so!
6B) Fill Column B with the basic Concatenate code by first putting the code into B2 and then clicking the bottom right corner of that cell and pulling down (this is a very useful thing to know, so don’t forget!). Then all you have to do is write the names of items in Column A and you’ll quickly have the item price.
“Wait a second… Red D-leather doesn’t cost 222gp each!” Yes, I’ll get to that
6C) Occasionally you’ll run into a small problem when using Concatenate. The problem is that sometimes (pretty rarely) when you search an item name it doesn’t show up in the second column so you end up getting the price of some other item that came up in that column instead. This is the case for Red D-leather. To fix the problem, as before, replace the 2 in the code “Table”, 2),2,3) with the actual column number:
=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",A2), "table", 2),5,3)
By searching Red D-leather on the Grand Exchange Database we learn that it comes up in the fifth column (see 4B screenshot above which demonstrates the same procedure, in case you’ve forgotten). So we replace the 2 in the code with a 5 and that’s that.
It can be annoying sometimes but it’s not too bad.
6D) If you also want to see how much the item in the ‘A’ column changed in price today just replace the final 3 in the code “Table”, 2),2,3) with a 4 and that will do it because when you search for an item on the Grand Exchange Database it is the fourth column that shows the Change in Price.
=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",A2), "table", 2),2,4)
All you have to do is count. I did it for you, just in case you don’t know how.
Pretty nifty, huh?
======================
7.0 [ Dealing with Ks and Ms ]
Any item worth 10,000 gold pieces or more will show up as 10k. Any item worth 1,000,000 gold pieces or more will show up as 1m. Google Docs doesn’t recognize that k stands for three zeros and that m stands for six so the calculations won’t work properly (I know. Annoying, right?). To fix this problem copy and paste the following code into whatever cell you want the fixed price to show up in:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( FAULTY CELL; "+";"") ; "k" ;"");"m";"") *IF(OR( RIGHT(FAULTY CELL;1)="k" ;RIGHT(FAULTY CELL;1)="m") ;sum(Index((right(FAULTY CELL)={"k","m"})*10^{3,6}));1)
If the “faulty cell” is B2, replace all the FAULTY CELLs with B2. There are four altogether.
If you can understand that code you can understand the meaning to life
======================
8.0 [ Final Words and Credits ]
I originally meant to add something about the IF and Filter functions which I’ve found useful but because of how large this guide already is and how difficult it is to explain those things to beginners, I’ll have to postpone that.
If you read this guide please give me your feedback on it. Feel free to ask me for help if you’re experiencing spreadsheet problems. If you make a spreadsheet yourself please be sure to post a link to it here so I can see it.
Much of the credit for this post goes to Brainymidget, the guy whose post on tip.it forums taught me the basics of creating Runescape spreadsheets. I was originally just going to link you to his post but I saw it was a bit outdated and missing some stuff I thought was important so I decided just to make my own.
Beginner’s Guide to Merchanting on The Grand Exchange
May 9th
Table of Contents
# 1.0 – Introduction
# 2.0 – The Basics
# 3.0 – Figuring out Buy and Sell Prices
# 4.0 – Important things to know before you start
# 5.0 – Automatic Item Buy and Sell Price Finder
# 6.0 – Credits
1.0 – Introduction
» Merchanting on Runescape isn’t nearly as hard as many people think. In fact, with just around 20 Million GP, you can make around 1 Million GP per day with very little hassle. Then, as the amount of money you have grows, you can make even more per day.
» Merchanting can be risky, but if you stick to the kind of Merchanting I’m going to tell you about there is little risk. This method, called Fundamental Trading, is not only very safe but also is one of the most profitable methods of Merchanting on Runescape – especially for beginners.
» I myself used this method when I began merchanting with 15M in 2009 and I have gained over 1 Billion GP from it. I still use it as my main method of Merchanting.
If you don’t believe I’ve made so much, just watch my video.
Make 
per day with just 
!!!
2.0 – The Basics
How Fundamental Trading works: Let’s use Raw Tuna as an example – 10 People each have 1K raw tuna that they want to sell quickly, so they all go the GE and offer to sell them at Minimum Price. You, the wise merchanter, also go to the GE and you put up an offer to buy Raw tuna at close to Minimum Price. After waiting for a while, you get all the Raw Tuna that the 10 people were selling. Next, you put in an offer to sell the Raw Tuna you bought near Minimum Price for a few GP over Market Price. These Raw Tuna are eventually sold to other players who put in offers to buy Raw Tuna at near Maximum Price because, unlike you – the wise merchanter – they are too impatient to wait for them to come in at or below Market Price.
The Kind of items that are good for Fundamental Trades: The more often the item is traded on the GE, the better the chance that people will constantly be selling low and buying high and, thus, the easier it will be to buy low and sell high. Look out especially for items that eventually either cease to exist or are changed into a different item. Such as: fish which is cooked and then eaten, bolts which are lost during range, Yew logs which are fletched to yew longs (u) then to yew longs when bowstring is added and finally to GP when they are alched.
Where to look for Fundamental Trades: The Top 100 Most Traded List is the best place to look for them because, you guessed it, this is a list of the items that are most traded and finding highly traded items is the key to Fundamental Merchanting. There are some good Fundamental Items that aren’t on the Top 100 Most Traded List but stick to just items on this list for now. Later on, after you’ve had a lot of experience, you can start searching for other items on The Grand Exchange Database.
Example: Though seeds are not on Top 100 Most Traded List they often are good to merch. After every GE update, I look up Seed on the Grand Exchange Database, look through them and see if any of them look good to merch (and I’ve found some good ones. I’m sure I’ve made at least 200m altogether from them).
3.0 – Figuring out Buy and Sell Prices
Figuring out the Buy Price: Here’s how I do it. I go to the Top 100 Most Traded List,choose any item that is more than 60gp each and I put in an offer at the Grand Exchange for one of that item at Market Price. If the item buys immediately at Market Price, I put in an offer to buy the Item for about 2.5% under Market Price also, I add the price it fell/rose that day to the Buy Price. If, however, the item does not buy immediately at Market Price I put in an offer for the item at 1 or 2 GP above Market Price.
Figuring out the Sell Price: When it comes time to sell, I simply sell the item for 2.5% over the Market Price and, as I did with the Buy Price, add the price it fell/rose that day to the Sell Price.
How to figure out what 2.5% below and above Market Price is: The easiest way to figure this out is to multiply the Market Price of an item by 0.975 and 1.025. So if you wanted to find the buy and sell prices of Tuna, whose market price is, say, 124GP, you multiply by 0.975 and 1.025 – 124 x 0.975 = 120.9 Buy, 124 x 1.025 = 127.1 Sell - so you would round up the numbers and buy at 121GP each and sell at 127 GP each.
Example for Buy and Sell: Say Yew Longbows’ Market Price dropped -9gp from 708GP to 699GP. First, put an offer for 1 Yew longbow at Market Price. If it doesn’t buy instantly, then buy for 1-2gp over Market Price. If it does buy instantly, then first figure out what is 2.5% below market price – 0.975*699=682GP – then add the price it rose fell today to 682 – -9 +682= 673. Thus, you would buy for 673 GP each. To figure out the sell price, first figure what is 2.5% above Market price – 1.025*699 = 716. Then, add the price it rose fell today and that’s your sell price – -9 +716= 707GP. Thus, you would sell for 707 GP.
I also have made a spreadsheet that you might find useful for finding buy/sell prices. It basically does all the calculations mentioned above for you and all you have to do is put in the item name. Here’s the link. The spreadsheet below (5.0) does basically the same thing except only for specific items which I know to work well using the 2.5% method. __________________________________________________________________________
4.0 – Important things to know before you start
There is a Trade Limit: You can only buy a certain amount an item on the Grand Exchange every 4 hours, depending on the type of item (see full list of how many of each sort of item you can buy every 4 hours). Also, when selling back an item, there will be a 4-hour delay before it starts to sell, so don’t freak out if it doesn’t start to sell immediately.
You need to be patient when Buying/Selling: When buying an item I reccomend that you wait at least 12 hours before changing the price. Of course you should only change the price if you haven’t gotten any of that item yet, or if you can tell that you’re not going to get any more at that price. When selling an item you should definitely wait at least 8-12 hours before changing the price. You have to wait 4 hours anyway before you even get a chance at selling them. Stubbornness is very good when it comes to selling.
You should always check an item’s graph before buying: Check the item’s graph on the Grand Exchange database on either Runescape.com or GrandExchangeCentral.com. Don’t buy the item if its falling in price drastically – especially if you’re a beginner. Also don’t buy it if its price is drastically rising or if the item is far above it’s usual price unless (1) you know for sure why it’s rising and (2) are confident that the item will not be crashing (i.e. falling back to it’s usual price) any time soon.
5.0 – Automatic Item Buy and Sell Price Finder
People always are asking me for good items and what price they should Buy/Sell for, so I made this spreadsheet that will do this automatically for you. It’s updated automatically whenever the Grand Exchange updates, so it’ll always be up to date.
P.S. this is a new thing so I’d appreciate your input on it. If you want to learn how to make your own spreadsheets like this one, see my Spreadsheet Making Guide.
6.0 – Credits
TedBundty – I did everything. If you find this guide helpful, I appreciate if you use the share/save button below to share with your friends or post a link to this guide on a Forum. Commenting is nice, too :).
Make 1.5m per day charging Glories w/Geyser Titan
May 8th
Just buy Glories for 1.5K under market price on GE, charge w/Geyser Titan (lvl 89 summoning required), and sell for 1K over market price. 2.5K profit per glory. Note that you can only buy upto 600 glories per day on the GE, so 2.5K profit X 600 glories per day = 1.5m total profit
Guide to killing Dagannoths in the Chaos Tunnels
May 2nd
I dyed my hair red!!!
…Anyway, here’s the guide lol. Let’s start with a few Questions I’m sure you’re asking.
Are the Dagannoths in the Chaos harder than the Dagannoths under the Light House?
1. Yes, they are much harder. Their max hit is 16, and you get attacked by a whole bunch at a time, so if you don’t have range pray on you will quickly die. (I myself have died here at least 4 times because I wasn’t paying attention to how many prayer points I had. Though, thankfully, I was always able to get my items back.)
Why bother killing the Dagannoths in the Chaos Tunnels instead of just killing the ones under the lighthouse?
1. Because the ones in the Chaos Tunnels are much less crowded (usually nobody but me is there).
2. The Dagannoths in the Chaos Tunnels have much better drops, including better charm drops (like a bunch of Crimsons).
3. Because I kill the Dagannoths in the Chaos Tunnels, so if you want to be like me(and who wouldn’t?), you should kill them there also :D.
Okay, I’m convinced. Tell me how!
What to wear: Wear armour that has a high prayer bonus. I usually bring Proselyte armour, Ardougne Cloak 3, Fury, Barrow Gloves, ROW, Whip, Dragonfire Shield or Falador Shield 3, Dragon Boots, and Slayer Helm.
What to Bring: Bring Prayer Potions (usually I bring about 4 or 5), and the Rest are optional: Falador Shield 3 (it restores your prayer points completely and, when wielded, gives you a +7 Prayer Bonus ), Holy Wrench (lets you gain more prayer points when you drink a prayer potion), Enhanced Excalibur (it’s special move heals 16HP, which is nice to have in case you get hit), Super Attack & Strength Potions, any kind of familiar (BoBs are good), Runes for alching, an anti-dragon shield (you have to go by a Bronze Dragon on your way there), and something to tele out with.
How to get there: Go North From Edgeville to lvl 4 Wild and go down the rope. You’ll be in a room with a bunch of Gargoyles, go to the West portal. Now you’re in a room with mummies, go the West portal in that room. Now you’re in a room with Zamorak Mages, take the East portal in this room. Now you’re in a room with Dust Devils, take the Portal that is a bit North of you in this room. Now your in a room wtih Black Demons, take the West portal in the next room. Now you’re in a room with earth warriors and a Bronze Dragon, go to the north portal in this room. Viola, you’re here!
Strategy for killing them: It’s pretty easy, basically just fight with Range pray on the whole time, drinking prayer pots when neccesary. If you don’t have enough inventory space then summon your BoB. If you get Fremmenik Helms, Swords, or Shields, you can alch them (they alch for around 3-4k ea).
Here are the drops that I got after one trip-
P.S. I got the idea to write this Guide when I finished a Dagannoth Slayer assignment (in the Chaos Tunnels), and Price Checked how much the drops I got were worth. Anyway, I saw that it was worth 222,222 GP and it made me laugh so I screenshoted it. Then I wanted to post the pic on my blog, but I figured I’d have to write a Guide if I did that, so here it is lol :)
Killing Spiritual Mages in GWD
Feb 24th
Me, TedBundty, killing Zammy spiritual mages in the God Wars Dungeon for a slayer task pretty good rewards, about 600k in all.
Loading ...