Need help counting positive trends in strings of data

[Chris Hoek]

I'm playing around with a spreadsheet with historical gold and silver pricing. The spreadsheet is arranged horizontally so that dates are all in a row, respective gold and silver pricing are below the dates also in rows. I'm going back several (about 5) years, so I have a lot of columns (around 1300).

I want to be able to count how many times gold/silver has gone on 2 day positive streaks, the count for 2 day positive streaks should not include streaks that were greater than 2 days
I want to be able to count how many times gold/silver has gone on 3 day positive streaks, the count for 3 day positive streaks should not include streaks that were greater than 3 days
I want to be able to count how many times gold/silver has gone on 4 day positive streaks, the count for 4 day positive streaks should not include streaks that were greater than 4 days
I want to be able to count how many times gold/silver has gone on 5 day positive streaks, the count for 5 day positive streaks should not include streaks that were greater than 5 days
I want to be able to count how many times gold/silver has gone on 6 day positive streaks, the count for 6 day positive streaks should not include streaks that were greater than 6 days, etc.

I want to be able to identify longest positive streaks in gold/silver data (how many days positive)

I will have some more trends I want to calculate, but I'm hoping I can figure those out myself once I have the above figured out.

I'm ok with inserting helper rows as needed.

Thank you in advance for any assistance you may be able to offer!

 

[duggie33]

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using, as the best solution often varies by version. Don’t forget to scroll down to save your changes.

Also, please provide some sample data and expected results. It seems like your data would be better if formatted in columns with dates being in a column and Gold and Silver in a row.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

 

[Chris Hoek]

Thank you Doug. Profile updated. Sample data below. Let me know if more info, details, data are needed.

Date	10/2/2018	10/3/2018	10/4/2018	10/5/2018	10/8/2018	10/9/2018	10/10/2018	10/11/2018	10/11/2018
Gold	$1,197.93	$1,200.03	$1,199.58	$1,200.13	$1,192.53	$1,186.88	$1,189.98	$1,210.63	$1,219.33
Silver	$14.55	$14.66	$14.51	$14.57	$14.24	$14.33	$14.26	$14.40	$14.56
Gold vs day before		$2.10	-$0.45	$0.55	-$7.60	-$5.65	$3.10	$20.65	$8.70
Silver vs day before		$0.11	-$0.15	$0.06	-$0.33	$0.09	-$0.07	$0.14	$0.17

 

[duggie33]

There are ways to make this cleaner, but this should demonstrate a way to get the information you are seeking.

Book_2023-10-17.xlsx
	L	M	N	O	P	Q	R	S	T	U	V
1	Date	Gold	Gold vs day before	Rising	Count	Streak ends	Silver	Silver vs day before	Rising	Count	Streak ends
2	10/2/2018	$1,197.93					$14.55
3	10/3/2018	$1,200.03	$2.10	TRUE	1		$14.66	$0.11	TRUE	1
4	10/4/2018	$1,199.58	($0.45)	FALSE	1		$14.51	($0.15)	FALSE	1
5	10/5/2018	$1,200.13	$0.55	TRUE	1		$14.57	$0.06	TRUE	1
6	10/8/2018	$1,192.53	($7.60)	FALSE	1		$14.24	($0.33)	FALSE	1
7	10/9/2018	$1,186.88	($5.65)	FALSE	2	end	$14.33	$0.09	TRUE	1
8	10/10/2018	$1,189.98	$3.10	TRUE	1		$14.26	($0.07)	FALSE	1
9	10/11/2018	$1,210.63	$20.65	TRUE	2		$14.40	$0.14	TRUE	1
10	10/12/2018	$1,219.33	$8.70	TRUE	3		$14.56	$0.16	TRUE	2
11	10/13/2018	$1,220.00	$0.67	TRUE	4		$14.60	$0.04	TRUE	3
12	10/14/2018	$1,221.00	$1.00	TRUE	5		$14.70	$0.10	TRUE	4	end
13	10/15/2018	$1,222.00	$1.00	TRUE	6	end	$14.50	($0.20)	FALSE	1
14	10/16/2018	$1,210.00	($12.00)	FALSE	1		$14.45	($0.05)	FALSE	2
15	10/17/2018	$1,205.00	($5.00)	FALSE	2	end	$14.40	($0.05)	FALSE	3
16	10/18/2018	$1,210.00	$5.00	TRUE	1		$14.35	($0.05)	FALSE	4
17	10/19/2018	$1,215.00	$5.00	TRUE	2		$14.30	($0.05)	FALSE	5
18	10/20/2018	$1,220.00	$5.00	TRUE	3		$14.25	($0.05)	FALSE	6
19	10/21/2018	$1,230.00	$10.00	TRUE	4	end	$14.20	($0.05)	FALSE	7	end
20
21
22		Gold Streak	Count				Silver Streak	Count
23		2	2				2	0
24		3	0				3	0
25		4	1				4	1
26		5	0				5	0
27		6	1				6	0
28		7	0				7	1
29		8	0				8	0
30		9	0				9	0
31		10	0				10	0
Sheet6

Cell Formulas
Range	Formula
N3:N19,S3:S19	N3	=M3-M2
O3:O19,T3:T19	O3	=M3>M2
Q3:Q19,V3:V19	Q3	=IF(P4<P3,"end","")
P4:P19,U4:U19	P4	=IF(O4=O3,P3+1,1)
N23:N31	N23	=COUNTIFS($P$3:$P$19,M23,$Q$3:$Q$19,"end")
S23:S31	S23	=COUNTIFS($U$3:$U$19,R23,$V$3:$V$19,"end")

I copied and paste/transposed your data to columns and also added in some additional "fake" data just to have more...

I hope this helps,

Doug

 

[Chris Hoek]

Doug, this should work just fine. I know why you flipped the data vertically. I will transpose it back horizontally and move on to the next problem. Thank you Sir!

 

[duggie33]

You're welcome, glad to help.

Doug