+ Reply to Thread
Results 1 to 17 of 17

rearranging table of bought/sold items

  1. #1
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    rearranging table of bought/sold items

    Hello, I'm trying to write a macro to rearrange a table into a usable pivot table.

    please take a look at the attachment for example.
    table.png

    The table on the left is the raw data which will be pasted in the sheet, table on the right the is the sorted data after the macro is run (in a new sheet, not replacing original data).

    The new table is sorting on round trips of quantities.
    for example the first row checks item is apple, 200 bought.
    Then check next row if same amount is sold (quantity is now 0), then print price sold price beside bought price
    move on to next row.

    *Notice the second transaction sums the quantities, and averages the prices.

    any help with how to do this would be appreciated. I feel overwhelmed that I need to have multiple indexes and offsets to print the new table, and am not too familiar with arrays yet.
    thank you

    edit:attached a sample worksheet
    edit2:updated sample worksheet
    Attached Files Attached Files
    Last edited by rez604; 01-29-2019 at 02:08 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: rearranging table of bought/sold items

    I think this could be done with helper columns and pivot tables but I need actual data to work on. I am not going to type in what you have in the pictures.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    Re: rearranging table of bought/sold items

    I've attached what you asked for.
    did not know about helper columns, looking up tutorials on those now.

    edit:found a problem when copying the table from my online source, there is a blank character (ascii 194) after every value in the cells, which will cause issues. I've removed them and updated the attached file again.
    anyone coming across this problem, i stumbled across it while testing if statements that were not returning correctly. copy that blank character, hit ctrl+H, paste it and leave the replace space blank to delete all instances.
    Last edited by rez604; 01-25-2019 at 03:49 PM.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: rearranging table of bought/sold items

    I think I need a walk through on how you got the various apple rows in the After sheet.

    I can see where each positive number is a "buy" quantity but I don't understand how you get the bought price.

    I tried taking all the negative quantities between the positives and average them, but that doesn't come out to the price sold either.

    Another question I have is how did you sell 60 apples when you only started with 20?

  5. #5
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    Re: rearranging table of bought/sold items

    no problem, thanks for the help. first, i noticed i made another mistake, row 2 on after sheet quantity should be (-)40 because we sold 40 apples first then bought them back.
    I've attached an updated & simplified color coded table with formulas to help with understanding.


    -the price is either a buy (positive quantity) or a sell (negative quantity).
    -the after sheet is a round trip on quantity (so that we arrive at zero quantity).

    so running through the first set,
    check if either we bought or sold first based on sign of quantity,
    apples | quantity 20 | price 6.87 = 20 apples bought at 6.87
    price bought is 6.87
    looking at next row,
    apples | quantity -20 | price 7.46 = 20 apples sold at 7.46
    20 bought & 20 sold add up to 0, price sold is 7.46, this set is done

    next one,
    apples | quantity -20 | price 7.50 = 20 apples sold at 7.50
    price sold is (currently) is 7.50
    next row,
    apples | quantity -20 | price 7.35 = 20 apples sold at 7.35
    quantity added up is -40 now, so we average prices (-20*7.50 + 20*7.35 / 40)
    price sold is now 7.425
    next row,
    apples | quantity 40 | price 7.04 = 40 apples bought at 7.04
    quantity sold + quantity bought is now 0, round trip complete
    price bought is 7.04
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: rearranging table of bought/sold items

    I have to get the logic as to why we select certain rows.

    The first step implies that if a positive value is immediately followed by an equal negative value it is a "set'.

    The second step implies that a set starts where the last set end and keeps running until there is a change in sign and the sum of the set to this point is equal to the inverse value of the next item. So -20 + -20 = -40 and the cell after these two is 40.

    I do not understand why you post the 7.425 in the price bought column or why the 7.04 is in the price sold column.

    Also I will need to know what to do in this situation. Suppose before row 4 = -20 and row 5 = -10 and row 6 = 40? What about the case where row 5 is -30? In other words, do I have to "split" rows. Are we dealing with a LIFO / FIFO situation here?

  7. #7
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    Re: rearranging table of bought/sold items

    regarding the second processed line, i made a mistake again, i'm sorry. those numbers should be swapped. because we have total of -40 sold at an average price of 7.425 and a total of 40 bought back at 7.04. i've updated the file again.

    once a set* is complete, everything is reset for the next row. so if you could imagine once the yellow set has been processed & printed in the new table, it is removed from the table and we start fresh with the next line which will make a new set.

    regarding your example situation, -20 + -10 = -30 sold, we will search until we gain +30 to make sold+bought to 0, while averaging all those prices. the way my raw data works, there will never be a situation where there will be a mismatch, example 20 bought and -30 sold on the same item will never happen, it will always balance out 20 in -20 out, etc.
    this is a average situation instead of lifo/fifo

    *[a fixed bought amount has been sold completely, or a sold amount has been bought up completely]
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: rearranging table of bought/sold items

    Quote Originally Posted by rez604 View Post
    Hello, I'm trying to write a macro to rearrange a table into a usable pivot table.
    Try this:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    Re: rearranging table of bought/sold items

    Akuini, thanks that is 90% of the way there. I tried it on a larger data set with a little more complexity and it broke down. I was not able to understand what your variables were place holders for to modify the code.
    problem i encountered:
    -the number of rows looks like it's restricted to 8, as in if there is more that 8 rows of data, the last row just sums up everything below it

    please take a look at new attached example.

    to make things clearer, i've also changed labels to help with understanding, this is for trading stocks.
    i've added a "position" column to hopefully help with the code. when the position reads 0, the "set" is done.
    there's also a time column now, Entry time is first placed trade for that set, Exit time is last placed trade for that set.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: rearranging table of bought/sold items

    You new sample has 5 column (not 3 as the first sample).
    When you tried it on a larger data set and it broke down, did you try using the 3 or 5 column version?

    I tried your new sample by using just 3 column, & it worked:
    So I need you to provide the sample that made the code went wrong.

    Excel 2013 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Item Quantity Price Item Quantity Price Bought Price sold
    2
    APPL
    100
    10,00
    APPL
    100
    10
    20
    3
    APPL
    -100
    20,00
    APPL
    200
    15
    20
    4
    APPL
    100
    10,00
    TSLA
    300
    20
    30
    5
    APPL
    100
    20,00
    APPL
    -100
    20
    10
    6
    APPL
    -200
    20,00
    APPL
    -200
    20
    15
    7
    TSLA
    100
    10,00
    TSLA
    -300
    30
    20
    8
    TSLA
    100
    20,00
    9
    TSLA
    -100
    20,00
    10
    TSLA
    100
    30,00
    11
    TSLA
    -100
    30,00
    12
    TSLA
    -100
    40,00
    13
    APPL
    -100
    10,00
    14
    APPL
    100
    20,00
    15
    APPL
    -100
    10,00
    16
    APPL
    -100
    20,00
    17
    APPL
    200
    20,00
    18
    TSLA
    -100
    10,00
    19
    TSLA
    -100
    20,00
    20
    TSLA
    100
    20,00
    21
    TSLA
    -100
    30,00
    22
    TSLA
    100
    30,00
    23
    TSLA
    100
    40,00
    Sheet: Sheet1
    Last edited by Akuini; 01-28-2019 at 07:23 PM.

  11. #11
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    Re: rearranging table of bought/sold items

    you are correct, there was something wrong with the value inputted in the 8th row that was causing a problem. thanks for the help, wouldnt let me rep you again.

    can we add in an extra line somewhere to swap price bought & price sold if the quantity value is negative once the whole thing is done?

    and if possible, can we also include another column "time"? Entry time is first placed trade for that set, Exit time is last placed trade for that set. see the above attached for example.
    appreciate the input, i'm still going through your code trying to learn how you are indexing.

    edit: i think i got the swap portion figured out:
    Please Login or Register  to view this content.
    Last edited by rez604; 01-28-2019 at 08:29 PM.

  12. #12
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: rearranging table of bought/sold items

    can we add in an extra line somewhere to swap price bought & price sold if the quantity value is negative once the whole thing is done?
    Not sure what you want, but since you've figured it out yourself then it's ok. But using the example above, what should the result look like?

    and if possible, can we also include another column "time"? Entry time is first placed trade for that set, Exit time is last placed trade for that set. see the above attached for example.
    appreciate the input, i'm still going through your code trying to learn how you are indexing.
    See what I can do, maybe tomorrow when I have time.

  13. #13
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    Re: rearranging table of bought/sold items

    regarding the swapping, it should look like the below. essentially once the table has been printed,
    check quantity column
    if value is negative
    swap price bought with price sold.

    if you have a better way to do it please do share
    Quote Originally Posted by Akuini View Post
    Excel 2013 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Item Quantity Price Item Quantity Price Bought Price sold
    2
    APPL
    100
    10,00
    APPL
    100
    10
    20
    3
    APPL
    -100
    20,00
    APPL
    200
    15
    20
    4
    APPL
    100
    10,00
    TSLA
    300
    20
    30
    5
    APPL
    100
    20,00
    APPL
    -100
    10
    20
    6
    APPL
    -200
    20,00
    APPL
    -200
    15
    20
    7
    TSLA
    100
    10,00
    TSLA
    -300
    20
    30
    8
    TSLA
    100
    20,00
    9
    TSLA
    -100
    20,00
    10
    TSLA
    100
    30,00
    11
    TSLA
    -100
    30,00
    12
    TSLA
    -100
    40,00
    13
    APPL
    -100
    10,00
    14
    APPL
    100
    20,00
    15
    APPL
    -100
    10,00
    16
    APPL
    -100
    20,00
    17
    APPL
    200
    20,00
    18
    TSLA
    -100
    10,00
    19
    TSLA
    -100
    20,00
    20
    TSLA
    100
    20,00
    21
    TSLA
    -100
    30,00
    22
    TSLA
    100
    30,00
    23
    TSLA
    100
    40,00
    Sheet: Sheet1
    thanks again for your help. if you're able to comment on your code at all it would be nice for learning.

  14. #14
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: rearranging table of bought/sold items

    Ok try this:
    I'm using 4 column set up.
    The code include the price swapping thing.

    Please Login or Register  to view this content.

    Result:
    Excel 2013 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    Stock Shares Price Time Item Shares Enter Time Price Entry Exit Time Price Exit
    2
    APPL
    100
    10,00
    9:33
    APPL
    100
    9:33
    10
    9:35
    20
    3
    APPL
    -100
    20,00
    9:35
    APPL
    200
    9:37
    15
    9:47
    20
    4
    APPL
    100
    10,00
    9:37
    TSLA
    300
    10:47
    20
    15:44
    30
    5
    APPL
    100
    20,00
    9:40
    APPL
    -100
    10:05
    10
    10:10
    20
    6
    APPL
    -200
    20,00
    9:47
    APPL
    -200
    10:33
    15
    11:29
    20
    7
    TSLA
    100
    10,00
    10:47
    TSLA
    -300
    9:50
    20
    11:52
    30
    8
    TSLA
    100
    20,00
    12:04
    9
    TSLA
    -100
    20,00
    12:12
    10
    TSLA
    100
    30,00
    12:15
    11
    TSLA
    -100
    30,00
    12:20
    12
    TSLA
    -100
    40,00
    15:44
    13
    APPL
    -100
    10,00
    10:05
    14
    APPL
    100
    20,00
    10:10
    15
    APPL
    -100
    10,00
    10:33
    16
    APPL
    -100
    20,00
    10:51
    17
    APPL
    200
    20,00
    11:29
    18
    TSLA
    -100
    10,00
    9:50
    19
    TSLA
    -100
    20,00
    10:01
    20
    TSLA
    100
    20,00
    10:03
    21
    TSLA
    -100
    30,00
    10:22
    22
    TSLA
    100
    30,00
    10:41
    23
    TSLA
    100
    40,00
    11:52
    Sheet: Sheet5

  15. #15
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: rearranging table of bought/sold items

    Note:
    Your sample in the workbook: the data is in a real table (not just range) & it has one last empty row i.e. row 24 (I think you're gonna use it for some calculation later). That empty row made the earlier code didn't work. So I revised the code to handle that situation, so it's oke to have that empty row, just don't do the calculation before running the code.

  16. #16
    Registered User
    Join Date
    01-24-2019
    Location
    vancouver, canada
    MS-Off Ver
    2010
    Posts
    8

    Re: rearranging table of bought/sold items

    Akuini, excellent. I tested it on a few different data sets and all came out correct. Thank you very much.

  17. #17
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: rearranging table of bought/sold items

    You're welcome, glad to help, & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Items bought together
    By MJB123 in forum Excel General
    Replies: 15
    Last Post: 10-01-2015, 09:09 AM
  2. Most frequent items bought together
    By MJB123 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-07-2015, 05:30 AM
  3. big table depreciation - what about items sold?
    By rprovideo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-26-2014, 08:17 PM
  4. [SOLVED] Number of items bought between 11:00 and 11:59 for example
    By carrolct in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-28-2014, 02:40 PM
  5. Replies: 1
    Last Post: 07-02-2013, 09:17 PM
  6. [SOLVED] Secondary Loan Pricing Calculation (price of loan being bought/sold)
    By Romsky in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 07:02 PM
  7. total of different items bought per month
    By yeats48 in forum Excel General
    Replies: 1
    Last Post: 03-11-2008, 08:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1