+ Reply to Thread
Results 1 to 7 of 7

1. consecutive days of profit and of losses 2. biggest profit loss since date

  1. #1
    Registered User
    Join Date
    05-10-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2008
    Posts
    3

    1. consecutive days of profit and of losses 2. biggest profit loss since date

    Hi community,

    I'm am new here. I am working for days on a larger spreadsheet. Answers to the following two question could boost the analysis and I am currently doing with the dataset and let me save a lot of time.


    A simple spreadsheet that lists dates, profits/looses

    A1=Date; B1=Profit/Loss
    A2-A100=Weekly Dates; B2-B100=Profit/Loss Data

    1. I would like to count consecutive days of profit and of losses = e.g. the output should display 3 if the last 3 consecutive weeks a profit was realised (10 if the last consecutive 10 weeks the Profit/Loss number was positive. If after the three weeks (10 weeks) the Profit/Loss data is negative the output should jump to zero and start counting from 0 again.
    Negative weeks should also be counted as above = output 3 after 3 three consecutive negative figures (or alternatively: -3 after three consecutive negative numbers), jumping to zero when a profit turns up.

    Could some help me with this task?

    2. Second output I would like to display: the last profit/loss figure represents the biggest profit/loss since date
    -> highest profit number since "according date needed here (wished output) and the profit on that date". In case of a loss: highest loss (lowest number) since "according date needed here and the loss on that date". The date is more important to me.

    Example:

    Date - Profit - Output I would like to display: Highest/Lowest number since
    1.28.2000 - 20 - 1.7.2000 25
    1.21.2000 - -5
    1.14.2000 - 17
    1.7.2000 - 25
    1.1.2000 - 4



    Any ideas? Thanks a lot in advance, I am thankful for any advices or ideas.
    Best Regards from Berlin,
    xbohemianx

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

    1. in C2 put in 0 (since with the first entry, the trend is unknown)
    in C3 put in:
    =IF(SIGN(B2)=SIGN(B3),C2+1,0)
    This is what you asked for, but I think you really don't want it to reset to 0, since it makes the second week of a positive/negative run show up as 1. I think you want the 'reset' to be a 1 instead of a 0 since it conveys a 1 week run of positive/negative. If you agree, then change the 0 in the equation above to a 1.

    2. Not entirely sure how you decide when to output the data. Every week will have a 'highest profit/loss since <date>', even if it is just the week before. In other words you would see something like this:
    Jan 500 Highest profit since Jan
    Feb 600 Highest profit since Jan
    Mar 700 Highest profit since Feb
    Apr 400 Highest profit since Apr
    May 450 Highest profit since Apr
    Jun 800 Highest profit since Mar
    Jul 800 Highest profit since ??? {is this Jun or Jul?}

    So, in essence, just about every line would have a statement. Maybe you could ignore the ones that are calling out the same month, but you would still get a lot of output.

    Or, using your data, why don't you have this for 1.14.2000:?
    1.14.2000 - 17 - 1.7.2000 25

    The rules aren't defined enough for me to work with.
    Last edited by Pauleyb; 05-10-2013 at 05:36 PM.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    05-10-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

    Quote Originally Posted by Pauleyb View Post
    1. in C2 put in 0 (since with the first entry, the trend is unknown)
    in C3 put in:
    =IF(SIGN(B2)=SIGN(B3),C2+1,0)
    This is what you asked for, but I think you really don't want it to reset to 0, since it makes the second week of a positive/negative run show up as 1. I think you want the 'reset' to be a 1 instead of a 0 since it conveys a 1 week run of positive/negative. If you agree, then change the 0 in the equation above to a 1.

    2. Not entirely sure how you decide when to output the data. Every week will have a 'highest profit/loss since <date>', even if it is just the week before. In other words you would see something like this:
    Jan 500 Highest profit since Jan
    Feb 600 Highest profit since Jan
    Mar 700 Highest profit since Feb
    Apr 400 Highest profit since Apr
    May 450 Highest profit since Apr
    Jun 800 Highest profit since Mar
    Jul 800 Highest profit since ??? {is this Jun or Jul?}

    So, in essence, just about every line would have a statement. Maybe you could ignore the ones that are calling out the same month, but you would still get a lot of output.

    Or, using your data, why don't you have this for 1.14.2000:?
    1.14.2000 - 17 - 1.7.2000 25

    The rules aren't defined enough for me to work with.
    Hi Pauleyb,


    thanks a lot for your solution for the first task. I tried a few versions yesterday and your thought represents the easiest solution. Thanks a lot! I still included a "helper" column that shows me: was the consecutive series a profit or loss run and simplifies pulling automated texts from the output. Your are of course perfectly right, with the value jumping back to 1 instead of 0.

    consecutive runs.JPG


    Regards the task to display the date of the "latest higher/lower number" in the data column I created the attached spreadsheet to explain the task.

    save.jpg

    You are right the output could be displayed over the whole column. I would only be interested in the ouput of the new data that is inserted, so I only wrote it down once in my example yesterday.

    So 4 outputs are possible either a new high or low is created with new data (Output: Highest profit; lowest profit) or the data is within the max.-min. range of the old data (here I would like to new when the latest higher profit or equal profit was realised, or when the latest lower loss or equal öpss was realised).
    The screenshot or the spreadsheet carry further information.



    Thanks again for your help, allready solely the solution for the first question is of great help to me!
    Regards
    bohemian

    - Attached: spreadsheet
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

    Maybe the use of a Pivot Table like this would help.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    05-10-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

    Quote Originally Posted by newdoverman View Post
    Maybe the use of a Pivot Table like this would help.
    Hi newdoverman,

    thanks for having a look at it and for your suggestion. I am afraid a pivot would not fit my needs though, it would create a better overview and allows a quick manual check for these numbers.

    I found something that helps and someone might have an idea how to reach the next step.
    The following formula is a good step into the direction I would like the output to look like:


    =IF(ROW(B2)=2,"",IF(B2>MAX($B$1:B1), "this value is
    the highest since " & TEXT(INDEX($A$1:A1,MATCH(MAX(
    $B$1:B1),$B$1:B1,0)), "mmmm yyyy"), IF(B2<MIN($B$1:B1),
    "this value is the lowest since " & TEXT(INDEX($A$1:A1,
    MATCH(MIN($B$1:B1),$B$1:B1,0)), "mmmm yyyy"),"")))

    ______________________________

    I am glad for this output - but it only gives me the "highest since" or "lowest since" for data that is stricking out the caputured data range. So only data new highs and lows are recognized as such.

    Please have a look at this screenshot or the attached spreadsheet. I receive the note: highest value/lowest value was reached but no information if the value is within the previous range.

    highest.jpg

    To give you an every day example: in the financial paper you can often read the price of an asset has reachest the highest/lowest price since then, e.g.: S&P500 reached highest level since - MMDDYYYY.
    Now if an asset has reached a signifikant high and a significant low over time and from then on trading only within that previous range -> the output of the above formula would not be interesting as no new highs or lows are reached. So receiving also information about the price movement within that range would allow comments like the stock X reached highest level since MMDDYYYY (also if it is "no all time high" within the dataset). That is exactly what I need the output to do. Highlight of new record numbers in the data set is good - but I need this further information.

    If anyone could help me that would mean a lot to me. Any ideas also if they only represent a partial solution are welcome.

    Attached you can find the spreadsheet again with the success of getting highest and lowest values as output.

    Thanks for the possibilty of sending in questions here in the forum and the quick replies of you newdoverman & Pauleyb!

    Greetings from Berlin!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

    This formula works for your example:
    =IF(B2>0,IF(B2=MAX($B$2:B2),"Highest Profit",INDEX($A$2:A2,MATCH(MAX($B$2:B2),$B$2:B2,0))),IF(B2=MIN($B$2:B2), "Highest Loss",INDEX($A$2:A2, MATCH(MIN($B$2:B2),$B$2:B2,0))))

    However, I think it does not work in a case you did not illustrate. What should it output if you change the value in cell B7 from a -30 to a 2? I believe the intent would be for it to show 1/29, but it shows 1/22. That case is where it gets tricky and would require more thought.

    BTW, you don't cover the case of a Profit/Loss of 0 for your column D. If it not possible to get a 0, then my initial equation could be simplified to something like IF(B2*B3>0,C2+1,1), but I chose to use SIGN since I assume there is a chance of 0 profit.

  7. #7
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

    This seems to match my expectations, maybe not yours though (see my comments above):
    In cell G2:
    Please Login or Register  to view this content.
    In cell G3:
    Please Login or Register  to view this content.
    use ctrl-shift-enter to enter the formula, then drag it down to G10.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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