How to find data on same date & add it us

  • I have a list of net returns per (stock) trade. Some of the trades had more than one trade per day. I want to locate the trades by date & add the total returns per date using macros so that it automatically finds the date with multiple trades & adds them up for a net total per date.


    I can't seem to get the HTML Maker Excel Add-in to work for me. So I have just gave an example below of what I am working with.
    (i.e.)
    07/08/03 07:00 $62.36 $640.00
    07/10/03 06:58 $62.08 $300.00
    07/11/03 07:42 $62.65 $400.00
    07/14/03 06:42 $63.50 $180.00
    07/17/03 06:56 $61.89 $2,860.00
    07/23/03 08:48 $64.36 $580.00
    07/24/03 06:38 $64.96 $960.00
    07/25/03 06:40 $65.38 ($920.00)
    07/25/03 08:28 $65.33 $1,820.00
    07/28/03 06:56 $66.22 $2,480.00
    07/29/03 06:36 $68.64 ($1,860.00)
    07/29/03 07:28 $68.37 ($580.00)
    07/29/03 10:30 $68.45 ($20.00)
    07/30/03 06:34 $68.90 ($380.00)
    07/30/03 09:36 $69.95 ($2,500.00)


    (Note: some days have only 1 & other days can have as much as 5-6 per date)
    (*Note: there are three columns-1.)date/time....2.)sell price....3.)net profit/loss)
    I want to identify the date in the first column & then get a net total per date.


    If my explanation is not clear enough let me know. Thanks in advance for any input/help.

  • Derk-
    Thanks for the advice. Except I've never used a Pivot Table. I gave it a shot by selecting the date row & net profit/loss (I don't care about the second row). And then selected Data > Pivot Table. It made the pivot table just fine. I tried grouping date & profit/loss together. But to no avail. I am sure I am doing it wrong.


    Could someone walk me through how to group the two so that it gives me a pivot table with the net totals like Derk's.


    Thanks.

  • In the pivot table layout part of the Pivot Table Wizard, drag the Date/Time Tile to the Row area and the Net tile to the Data area and show the pivot table. The dates and times should run down the first column and the Nets down the second column. Then right click in the Date column and select Group and Outline>Group. Select Days and then OK. That should do it.

  • Derk-


    I hate to be a numbskull about this. But I am with on dragging & dropping to make the pivot table (dates in row.....nets in data). But when I right click the dates column I don't get "Group and Outline>Group"....I get "Group and Show Details".


    I'm lost I'm not finding "group and outline"?


    What am I missing?

  • I don't know if this matters or not. But when the pivot table is set up the dates are in the first column like you said, and the nets are in the second column and they are all in order matched up OK. Except the first net is on top of the first date vs. to the right of it in the net column. And I can't seem to get it to the right in the net column?

  • Maybe Excel changed the title in newer versions (I'm on Excel 97). "Group and Show Details" sounds like a replacement. When you select that do you get a sub menu that has Group as one of the choices? If so, that is what you want.


    I suspect the first net is on top of the first date because you don't have labels in the data you selected, so Excel is taking the first entry as a label. Try adding labels and reselect your table to include the labels.

  • Maybe I should try posting a thread asking how to get this pivot table to work?


    Would it help if I emailed you an attachment of the workbook? Or would it even help, since you are using 97.

  • Probably the nets are being "counted" rather than "summed." Go back to the layout, doubleclick on the Net tile and select Sum.
    To see if the date are excel dates try adding one to one of them and see if it increase by a day (or format them af numbers and see if they change).


    I'm sorry you are having such a poor intro to pivot tables. They are quite useful and worth mastering, so don't give up yet.


    As to e-mail, I would rather keep to the forum if possible so all can benefit from the trouble-shooting. Let's see what the next few hours bring before resorting to e-mail.

  • Derk-


    1.) Changing the nets to sum vs. counting corrected that problem.


    2.) As far as the dates go-I did not understand "try adding one to one of them and see if it increase by a day "......but I did try formatting the cells by changing them to numbers and it did not change anything. (Note: I highlighted the first column-which is the dates-in the pivot table & right clicked > format cells). Is this what you meant? If so, they did not change.


    Thank you for your patience & your consideration to even stay with this problem. I very much appreciate it.

  • OK. The dates are a string of text. That's why they couldn't be grouped. To convert them from text to an excel date, in a new column of your data (not the pivot table) enter the formula =DATEVALUE(A2)
    to convert the A2 (adjust to the proper reference for your first date/time cell). This will also set the time to midnight. Drag the formula as needed to convert all of the date/times and then use this column in your pivot table. Since it is now all in dates at midnight, you will only need to group if you want to see weeks or months or quarters etc.

  • That makes sense, but for some reason it is not recognizing "=DATEVALUE(a2)". I understand what you are saying. I put the dates in column A & typed the formula in column B. I typed =DATEVALUE(a2) in cell B2- but it gave me #VALUE!. For some reason it does not recognize it?


    It's strange because I can sort by those dates.


    I played around with the dates some. I found there is a space before each date. I don't know if it affects anything. I went in and used this fomula to trim & substitute "=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"(","-"),")",""))," ","")*1"
    But even then it did not work.


    It still gives me the same message that "cannot group that selection".


    Do you think it might have something to do with the time? Is there a way to eliminate the times?

  • Now I am really puzzled. Ifthere is a space before the date, then it is text. If you get the #Value with =Datevalue that suggests it's a number. With one of your dates in A2 put the formul =A2+1 in some cell and see what happens. If the date is text, you should get a #Value! error, if its an Excel date you should get a number which when formated yields a date. See example below.
    <SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - Book1</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl97 : OS = Windows NT 4 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;(<U&gt;A</U&gt;)bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb658016&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb431795.sltNb305190.value);' type=button value="Copy Formula" name=btCb074442&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb431795&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value" name=sltNb305190&gt;<OPTION value==DATEVALUE(B2) selected&gt;C2<OPTION value==B2+1&gt;D2<OPTION value==DATEVALUE(B3)&gt;C3<OPTION value==B3+1&gt;D3<OPTION value==DATEVALUE(B4)&gt;C4<OPTION value==B4+1&gt;D4<OPTION value==DATEVALUE(B5)&gt;C5<OPTION value==B5+1&gt;D5</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value==DATEVALUE(B2) name=txbFb428726&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;Type</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;Date</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;DateValue</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;+1</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;Excel&nbsp;Date</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;7/8/2003&nbsp;7:00&nbsp;AM</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb431795.sltNb305190.options[0].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;#VALUE!</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;<A onclick="document.formFb431795.sltNb305190.options[1].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;7/9/2003&nbsp;7:00&nbsp;AM</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;3</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;Text</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;&nbsp;7/10/2003&nbsp;&nbsp;6:58&nbsp;AM</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb431795.sltNb305190.options[2].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;7/10/2003&nbsp;12:00&nbsp;AM</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb431795.sltNb305190.options[3].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;#VALUE!</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;4</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;Excel&nbsp;Date</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;7/11/2003&nbsp;7:42&nbsp;AM</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb431795.sltNb305190.options[4].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;#VALUE!</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;<A onclick="document.formFb431795.sltNb305190.options[5].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;7/12/2003&nbsp;7:42&nbsp;AM</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;5</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;Text</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;&nbsp;7/14/2003&nbsp;&nbsp;6:42&nbsp;AM</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb431795.sltNb305190.options[6].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;7/14/2003&nbsp;12:00&nbsp;AM</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb431795.sltNb305190.options[7].selected=true; document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value;" href="#javascript:void(0);"&gt;#VALUE!</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet3</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker 2.41] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!