Collect data from multiple sources to form a table of data in a specific order

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Hi there Excel Guru's,

Do excuse the title of this one...

Below is a table of data.
What I need to do is gather the data from respective cells and collate this data into a simple format to ascertain the best values

E2.PNG


For example
Gather all the setting no.s (ranked biggest to smallest)
The pair is related to the setting number
Account balance next in relation to the setting number
The total trades - again in relation to the setting number
Etc...

Drafted example (Possible layout)

account balance£88126£64287£57001£51085
setting no. (in order biggest > smallest)76572821
total trades386010196
pairAUDJPYAUDCADAUDCHFAUDNZD
I may require more data gathering in this row


There are 28 pairs (columns) of data.
The idea is to understand the setting no. and which setting (group of numbers) produces the best return.

I hope this makes sense.

I have enclosed a copy, but its values only - so no formulas
The reason why some of the columns show #DIV/0! is the data produced at setting one = zero. I.E The report displayed no values for this setting.
I know how to set the cell to blank or 0

If you require further info, please just message me.

My thanks and appreciation
Copperwasher
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Cannot manipulate data from a picture Please reload the data to this site using XL2BB. Also, looking at your picture, you have what appears to be lots of merged cells. Look at this link on why this is not a good practice.


 
Upvote 0
Cannot manipulate data from a picture Please reload the data to this site using XL2BB. Also, looking at your picture, you have what appears to be lots of merged cells. Look at this link on why this is not a good practice.




MrExcel 1000. CCIColour Period1 Batch1.1.1.100 TradesAll OptiResultsReport V8.0.xlsx
A
25
ReportSingle Performance


Here you go... first time using the capture add-in...

Hopefully its worked
 
Upvote 0
You need to highlight the range you wish to upload first and then use XL2BB. You have uploaded an empty range.
 
Upvote 0
You need to highlight the range you wish to upload first and then use XL2BB. You have uploaded an empty range.

MrExcel 1000. CCIColour Period1 Batch1.1.1.100 TradesAll OptiResultsReport V8.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
128AUDCADAUDCHFAUDJPYAUDNZDAUDUSDCADCHFCADJPYCHFJPYEURAUDEURCADEURCHFEURGBPEURJPYEURNZDEURUSDGBPAUDGBPCADGBPCHFGBPJPYGBPNZDGBPUSDNZDCADNZDCHFNZDJPYNZDUSDUSDCADUSDCHFUSDJPY
2Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%
3setting no.57287621172962311341144100114694771126275116
4
5Ratio of winning vs losing trades1.221.061.921.04#DIV/0!1.391.131.15#DIV/0!#DIV/0!1.34#DIV/0!#DIV/0!1.431.21#DIV/0!#DIV/0!1.581.571.10#DIV/0!#DIV/0!1.271.391.13#DIV/0!#DIV/0!1.13
6Days per trade cycle24143815#DIV/0!222313#DIV/0!#DIV/0!19#DIV/0!#DIV/0!2928#DIV/0!#DIV/0!304135#DIV/0!#DIV/0!14207#DIV/0!#DIV/0!7
7
8Profit trades3355%5251%2566%4951%0#DIV/0!3958%3453%6253%0#DIV/0!0#DIV/0!4357%0#DIV/0!0#DIV/0!3059%2955%0#DIV/0!0#DIV/0!3061%2261%2252%0#DIV/0!0#DIV/0!5756%4358%11353%0#DIV/0!0#DIV/0!11053%
9Loss trades2745%4949%1334%4749%0#DIV/0!2842%3047%5447%0#DIV/0!0#DIV/0!3243%0#DIV/0!0#DIV/0!2141%2445%0#DIV/0!0#DIV/0!1939%1439%2048%0#DIV/0!0#DIV/0!4544%3142%10047%0#DIV/0!0#DIV/0!9747%
10Total trades60100%101100%38100%96100%0#DIV/0!67100%64100%116100%0#DIV/0!0#DIV/0!75100%0#DIV/0!0#DIV/0!51100%53100%0#DIV/0!0#DIV/0!49100%36100%42100%0#DIV/0!0#DIV/0!102100%74100%213100%0#DIV/0!0#DIV/0!207100%
11
12Initial deposit (check)£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000
13Gross profit£96,87154%£130,89751%£89,44164%£116,48750%£0#DIV/0!£126,02258%£98,04752%£221,88952%£0#DIV/0!£0#DIV/0!£147,91957%£0#DIV/0!£0#DIV/0!£85,45758%£80,78154%£0#DIV/0!£0#DIV/0!£95,26660%£57,09761%£58,79351%£0#DIV/0!£0#DIV/0!£184,28155%£150,39257%£406,32353%£0#DIV/0!£0#DIV/0!£318,04753%
14Gross loss-£82,58446%-£123,89649%-£51,31536%-£115,40350%£0#DIV/0!-£91,17942%-£90,16648%-£203,50248%£0#DIV/0!£0#DIV/0!-£113,63443%£0#DIV/0!£0#DIV/0!-£61,28442%-£68,38646%£0#DIV/0!£0#DIV/0!-£63,23240%-£36,82639%-£55,38949%£0#DIV/0!£0#DIV/0!-£148,60445%-£111,22843%-£365,18147%£0#DIV/0!£0#DIV/0!-£285,11047%
15Total P & L£179,455100%£254,794100%£140,756100%£231,890100%£0#DIV/0!£217,201100%£188,213100%£425,390100%£0#DIV/0!£0#DIV/0!£261,554100%£0#DIV/0!£0#DIV/0!£146,740100%£149,167100%£0#DIV/0!£0#DIV/0!£158,498100%£93,923100%£114,183100%£0#DIV/0!£0#DIV/0!£332,886100%£261,620100%£771,504100%£0#DIV/0!£0#DIV/0!£603,158100%
16
17
18Ratio of profit vs loss1.171.061.741.01#DIV/0!1.381.091.09#DIV/0!#DIV/0!1.30#DIV/0!#DIV/0!1.391.18#DIV/0!#DIV/0!1.511.551.06#DIV/0!#DIV/0!1.241.351.11#DIV/0!#DIV/0!1.12
19Profit or loss£14,28729%£7,00114%£38,12676%£1,0852%£00%£34,84370%£7,88116%£18,38737%£00%£00%£34,28569%£00%£00%£24,17348%£12,39525%£00%£00%£32,03564%£20,27041%£3,4047%£00%£00%£35,67771%£39,16478%£41,14282%£00%£00%£32,93766%
20Account balance£64,287£57,001£88,126£51,085£50,000£84,843£57,881£68,387£50,000£50,000£84,285£50,000£50,000£74,173£62,395£50,000£50,000£82,035£70,270£53,404£50,000£50,000£85,677£89,164£91,142£50,000£50,000£82,937
21
22
23
24
25
ReportSingle Performance
Cell Formulas
RangeFormula
A1A1=COUNTA(B1:BE1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B20:BE20Cell Value=50000textNO
B20:XFD20Cell Value>50000textNO
B20:XFD20Cell Value<50000textNO
B5,D5,BD5,BB5,AZ5,AX5,AV5,AT5,AR5,AP5,AN5,AL5,AJ5,AH5,AF5,AD5,AB5,Z5,X5,V5,T5,R5,P5,N5,L5,J5,H5,F5Cell Value<1textNO
B5,D5,BD5,BB5,AZ5,AX5,AV5,AT5,AR5,AP5,AN5,AL5,AJ5,AH5,AF5,AD5,AB5,Z5,X5,V5,T5,R5,P5,N5,L5,J5,H5,F5Cell Value>1textNO
B5,D5,BD5,BB5,AZ5,AX5,AV5,AT5,AR5,AP5,AN5,AL5,AJ5,AH5,AF5,AD5,AB5,Z5,X5,V5,T5,R5,P5,N5,L5,J5,H5,F5Cell Value=1textNO
B18,BD18,BB18,AZ18,AX18,AV18,AT18,AR18,AP18,AN18,AL18,AJ18,AH18,AF18,AD18,AB18,Z18,X18,V18,T18,R18,P18,N18,L18,J18,H18,F18,D18Cell Value<1textNO
B18,BD18,BB18,AZ18,AX18,AV18,AT18,AR18,AP18,AN18,AL18,AJ18,AH18,AF18,AD18,AB18,Z18,X18,V18,T18,R18,P18,N18,L18,J18,H18,F18,D18Cell Value>1textNO
B18,BD18,BB18,AZ18,AX18,AV18,AT18,AR18,AP18,AN18,AL18,AJ18,AH18,AF18,AD18,AB18,Z18,X18,V18,T18,R18,P18,N18,L18,J18,H18,F18,D18Cell Value=1textNO


Opps, Contents highlighted, copy and pasted.
 
Upvote 0
Using Power Query/Get and Transform found on the DATA tab
Book6
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15Column16Column17Column18Column19Column20Column21Column22Column23Column24Column25Column26Column27Column28Column29
2PairsEURUSDCADJPYGBPJPYGBPNZDAUDJPYCADCHFAUDCADGBPCHFEURNZDEURCHFAUDCHFNZDJPYNZDCHFCHFJPYAUDNZDUSDJPYNZDUSDEURJPYEURGBPEURCADUSDCHFUSDCADGBPAUDAUDUSDGBPCADNZDCADGBPUSDEURAUD
3setting no.10096947776725746443428272623216511111111111
4Total trades53643642386760495175101741021169620721300000000000
5Account balance62395.3957881.3470270.3953403.7388126.2784843.4364286.5382034.5474172.9184284.9757001.1689163.5885677.1368387.2851084.8182937.1191142.215000050000500005000050000500005000050000500005000050000
Sheet2


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"28", type text}, {"AUDCAD", type any}, {"Column1", type any}, {"AUDCHF", type any}, {"Column2", type any}, {"AUDJPY", type any}, {"Column3", type any}, {"AUDNZD", type any}, {"Column4", type any}, {"AUDUSD", type any}, {"Column5", type any}, {"CADCHF", type any}, {"Column6", type any}, {"CADJPY", type any}, {"Column7", type any}, {"CHFJPY", type any}, {"Column8", type any}, {"EURAUD", type any}, {"Column9", type any}, {"EURCAD", type any}, {"Column10", type any}, {"EURCHF", type any}, {"Column11", type any}, {"EURGBP", type any}, {"Column12", type any}, {"EURJPY", type any}, {"Column13", type any}, {"EURNZD", type any}, {"Column14", type any}, {"EURUSD", type any}, {"Column15", type any}, {"GBPAUD", type any}, {"Column16", type any}, {"GBPCAD", type any}, {"Column17", type any}, {"GBPCHF", type any}, {"Column18", type any}, {"GBPJPY", type any}, {"Column19", type any}, {"GBPNZD", type any}, {"Column20", type any}, {"GBPUSD", type any}, {"Column21", type any}, {"NZDCAD", type any}, {"Column22", type any}, {"NZDCHF", type any}, {"Column23", type any}, {"NZDJPY", type any}, {"Column24", type any}, {"NZDUSD", type any}, {"Column25", type any}, {"USDCAD", type any}, {"Column26", type any}, {"USDCHF", type any}, {"Column27", type any}, {"USDJPY", type any}, {"Column28", type any}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Column1] = "28" or [Column1] = "Account balance" or [Column1] = "setting no." or [Column1] = "Total trades")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","28","Pairs",Replacer.ReplaceText,{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Transposed Table", each not Text.StartsWith([Column1], "Column")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Column2", Order.Descending}}),
    #"Transposed Table1" = Table.Transpose(#"Sorted Rows")
in
    #"Transposed Table1"
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top