I need help with a particular Excel Function and Formula

Hey Guys,

I was wondering if it was possible to form a formula based on my following requirements:

Currently, I have two spreadsheets, one is a statistical spreadsheet and one is a pip count sheet.

On the pip count sheet i type in the pair i use, the system i use and the pip count for that trade.

In the Statiscal Spreadsheet, i have all the stats, such as % of winning trades. etc

I organised my stats spreadsheet so that it is arranged by pair as well as system.

I want to be able to type one thing into my pip count spreadsheet and have it replicated in my stats spreadsheet without me entering it in twice.

So for example, I want to be able to place a "EURUSD" trade under the "POURI" System, and the amount of pips earned from that trade in my pip count sheet. And i want this information to be sorted and filled under the POURI - EURUSD system section, in the stats spreadsheet - showing the amount of pips gained and how many trades have been done for that particular trade.

Also, I would like it to identify whether the trade has been negative or positive and arrange it in the "Winning pips" and "losing pips" section of my stats spreadsheet.

Ive done all the formulas for the stats spreadsheet, and the pip count spreadsheet - the only thing i am having trouble on is producing something that can identify the above and place it in its appropriate cells in the following sheet

Cheers

Rusty

Use the excel help (F1) then type in "link cells" in the search function.
The topic you are looking for in under "create a link between cells in different workbooks"

Hope this help.

The above idea is good, but can get messy if you move your files around.

I would place your 2 spreadsheets as tabs in the one workbook. Then its just a matter of referencing tabs, if you are working in a tab and want to get data from another tab called 'PIPS', then the formula in your first tab looks something like

=PIPS!A1

This will return whatever value is in cell A1 in the PIPS tab.

As far as placing winning and losing trades in different columns i will return later and go through that unless someone else posts before me.

To get the number of winning/loosing trades use the countif() function.

= (just point your mouse to another sheet/spreadsheet that you want)
=sumif
=dsum
=dcount
and array type of function, for complete reference, just press f1

limitation in array, you have to define the field to be filled with data, and it is mostly 1-1 corresponden

oh yes, to open both spreadsheet at once, open both and save workspace

the easiest way, arrange the coloum and row so that you can just copy & paste it

Does anyone know of any good Excel tutorials floating around? I could see Excel being of much use to us FF denizens.

Excel is an extremely powerful program, and i think it could be utilised by many traders to not only improve their record keeping, but also for risk management, profit/loss projections, statistical info etc. I have noticed a couple of spreadsheets floating around FF doing some of these jobs.

As far as good tutorials go, from what i've found, many of these tutorials give you good fundamentals on excel functions, but fail to get into the powerful stuff. I've found only by lots of experimenting(plus i use it every day for my day job) can you really get into the meaningful functionality of excel.

If anyone does know of a good tute tho, i'd love to see it

Here is a file of the excel spreadsheet im trying to format.

Notice there is a sheet named "traders datasheet" and another sheet named "stats"

What i would like to do - is enter data into the "traders datasheet" and then it replicates and sorts itself into the "stats" sheet - without me having to enter it in twice.

All the formula's for the stats sheet have been done - in terms of calculating %'s and so on. I just need a way that the stats sheet can identify say for example....

When i enter a EURJPY trade under the POURI system for -17 pips.

I want this to replicate in the stats sheet under the POURI section, on the EURUSD row as - one trade, that is a loser, and be entered under the losing pips coloumn.

have a look at the spreadsheet it should make things clearer.

Cheers, and thankyou all so much

rusty

Having looked at it briefly, there's a couple of things I note.

1. The ideas you're suggesting are great, as you'll really break down the overall picture of how your strategies are performing.

2. It would need to be set up differently though, no offence intended of course. But i see the need for another tab, a raw data tab, of which then flows into your Trader's Datasheet & Stats sheet.

3. When writing this spreadsheet, you'd need to consider if your strategies would come and go, and if so you'd need to decide whether you want to retain, replace or add to the strategies listed.

I think it will come down to how much time you're willing to put into creating this thing..

I agree a raw data tab would probably need to be implemented.

I just need help now with the formulas and functions of entering data so that it is appropriately entered into the Traders Datasheet - as im no wizz on excel.

I have time - i just need to basically understand how to you create a rule, where the cell only accepts cells with certain words written, and so on.

any ideas on this?

I use openoffice.org. Its free and always improving. The program that rivals Excel is Calc. The forum is excellent. You will find tutoral links and can ask any question. Also do a search on calc tutoral.

http://www.oooforum.org/forum/viewforum.phtml?f=3

Research IF functions, and nested IF functions.. and countif, sumif,

Thanks guys for your help

i figured it out using the Countif and Sumif functions.

Ill post my spreadsheet once im done - could take me a while!

cheers

Rusty

Still using XL 2000.
Can you tell me if the "IF" statement has grown to > 7 Nested?

Not sure, i think its still limited to 7..

But once you get to that level it might be wise to come at it from a different angle, like the VLOOKUP function, which basically returns a value from a table of data

>7 nested only possible in 2007

Hey.

I use Excel quite extensive (for investing and lots of other stuff).

It's a great tool. If you haven't managed yet (SUMIF would be a good function to try, or maybe just IF and setting the parameters), I could help you.

Let me know.

Regards.