1. Oldschool RuneScape 2007 Flip Chart requires an account to use. Please register and account to access all of our features by clicking here.

How to make a basic google spreadsheet document for tracking flipping

Discussion in 'Guides and Tips' started by Wice, Mar 24, 2016.

  1. So just to start off I labeled the columns we will be using at the top.
    http://imgur.com/pFcbwhC
    Next, I'm just gunna input a random item, quantity, buy price, and sell price since these don't require any calculation or formatting on your part. (note: these prices are not accurate so don't bother using them)
    http://imgur.com/Xun1P9e
    First, let's set up the profit you receive from your successful flip. To do this we subtract the sell price from the buy price and multiply the difference by the amount we bought. To do this in spreadsheet the formula looks something like "=(D2-C2)*B2"
    http://imgur.com/xoems28
    Now we copy this to the lines below it by simply dragging the small blue box in the lower right corner of the E2 square. You can drag it down to say E100 (i dragged it down toE6 just to show it). You'll notice that the profit for your second item should update automatically and any blanks rows show a zero.
    http://imgur.com/TXT948j
    For the next step you can skip it if you don't want the margin percent, but I like seeing it so I'll include it here. The formula might be confusing so ill show it before describing it "=ROUND((((D2/C2)-1)*100),2)". So first the formula divides the sell price by the sell price to give the percentage difference between the two prices. The percentage is displayed in the decimal form (ex. 1.30). We have to get rid of the one since percentages are numbers between 0 and 1. (therefore, 1.30 - 1 = 0.30). Next to get a decimal as a percent we just simply multiply it by 100 (0.30 x 100 = 30). The ROUND(margin,2) just rounds the decimal to 2 places because you don't want to see a percentage with 10 trailing decimals behind it. (ex. ROUND(33.3333333333,2) = 33.33). And last but not least drag the blue box down to distribute the equation throughout the column. You'll notice it says #DIV/0!. This just means the equation has no answer since you have no value for buy price and this results in division by zero which mathematically is not possible.
    http://imgur.com/4wGBZTG
    The last thing we need is the total so we know how much flipping has made us. To do this simply put the formula "=sum(E2:E1000)". This just adds up every single value in the profit column. (if you happen to get to row 1000 just change it to "=sum(E2:E2000)").
    http://imgur.com/rP7dsG3
    And you're done. If you'd like to make your spreadsheet a little more pretty just google what you need or ask me in game and i'll help you with what you need.
    Hope this helps :)
     
    Hot Merchant and Hugh Jasshol like this.
  2. Thanks, very helpful!
     
    Wice likes this.
  3. Np :)
     
  4. Cald you're a god, thanks a zillion.
     
    Wice likes this.
  5. Np :) Always happy to help
     
    Hugh Jasshol likes this.
  6. Just copied exactly what you said to do, made a nice, simply, efficient spreadsheet in under 5 minutes. Glorious work Cald!
     
    Wice likes this.
  7. Very useful! I recommend that you flesh this out a bit more then make the gyazo links actual images (when you edit it, press more options then below is an option to upload files, where you can input images) Once you've done this and added a little more detail then I think we could sticky this possibly?? Who knows, thats a call for staff! but It's deffo useful
     
    Wice likes this.
  8. Great guide. Having a well put-together spreadsheet makes keeping track of your flips so much easier. A few extra things that I like to do in mine:

    ROI
    A column for Return on Investment (ROI). Just tells you what percentage of the gold that you put in you will get back as profit. You can do that with the formula "=(SellPrice/BuyPrice)-1

    Auto-Calculate Daily Profits
    I have a separate page that lists my daily profit and a running total of my profit to date that looks like
    this
    [​IMG]
    The formula you see there looks a lot more complicated than it is. I use a function called SUMIF. You have to tell SUMIF three things: A list of cells to look at, something to look for, and a list of cells to add together if it finds what it is looking for. So in this example, The first part, "Log!A6:A1018", means go to the sheet called "Log" which is where all of my logs are and to look at the "Date" column (Column A in my case). The numbers don't matter, just pick one that is toward the beginning, and one that is past the end of your data so that it covers the entire range. The next part, "A7", is a reference to the cell in the picture that contains "3/24/2016". The spreadsheet will check all of the cells in the range from the first part for this value, any that match will be used for the third part. "Log!G6:G1018" is very similar to the first part, except it is the profit column from my log. Any rows that were matched by date will be summed up and displayed as the total profit for the day. You can repeat this process for each day and the spreadsheet will automatically sum up your profit without the need to manually select ranges
     
  9. Great idea Black, I'll have to add that to my speadsheet!
     
    Wice likes this.
  10. Thanks I'll make sure to do this
     
  11. Thanks for the suggestion, I think I will do this as well
     
  12. #12 Vice, May 20, 2016
    Last edited: May 20, 2016
    For what Viper wrote about the auto-calculate daily profits, does it automatically put in the dates and the profit? Currently in my spreadsheet I'm able to have the profit automatically added every time a new profit datum is input into the first sheet, but it does not input the corresponding date from the first sheet into the second sheet. Do I have to manually enter the date or what is the formula I have to use?

    Edit: Do I input the same formula so it does take the date? I believe that is what I'm doing wrong.
     
  13. hey great guide!
    but i can't figure out how blackviper91 is using the SUMIF stuff to get the daily profits.
    can some1 help me with that?
     
  14. Thanks for the suggestion i'll try this out
     

Share This Page