Microsoft Excel Q&A

[hhse]

sorry the first sheet was missing the middle formula - use this one: View attachment 54172

Pivot tables should work buddy 1 minute job.

Handy tips:
Select date as field, click in column, then go design tab & use group data and choose months.
Then drag down returns 3 - set one as average, another as min and max.


Refer to attachment.

 

[pixel]

SUMIF(F21:F69*(E21:E69+L21:L69)+J21:J69*(I21:I69+M21:M69),K21:K69>today()-30))

I tried the above but I have an error...I know that what your saying is correct, I am just not implementing it correctly.

Instead of confirming the formula with the Enter key, you need to use the triple combo Ctrl-Shift-Enter.
I would type
=SUM(IF(K21:K69>today()-30,F21:F69*(E21:E69+L21:L69)+J21:J69*(I21:I69+M21:M69)))
and after typing (or copy-paste) hold down Ctrl and Shift while hitting the Enter key.
That will turn a formula into an array formula.

If you don't want to put your spreadsheet on public display, you can IM or email it to me.
It'll take me 5 minutes to key in the proper formula.

example attached:

View attachment sum.if.xls

 

[kid hustlr]

How would I calculate a max drawdown in excel. say ive got 50 trades and 50 results from those trades ranging from say -10 to +100. How would I calculate maxDD?

 

[skc]

How would I calculate a max drawdown in excel. say ive got 50 trades and 50 results from those trades ranging from say -10 to +100. How would I calculate maxDD?

All words in Blue meant to be the formula to be entered into the cell.

Say column A you have the 50 rows of P&L.

Use column B to calculate the cumulative P&L. So B1=A1, B2=B1+A2 (and fill down to the bottom)

Then use column C to calcuate the high water mark of the cumulative P&L. So C1 =B1. C2 =IF(B2>C1,B2,C1) then fill down.

Then lastly, use column D to calcuate the drawdown amount. D1 =B1-C1 then fill down.

You can then plot column D for a little drawdown waterfall. Or you can plot column B and C for a nice equity curve and the highwater envelope.

 

[kid hustlr]

ta mate. i got as far as the cumulative part on my own haha

 

[Trembling Hand]

Hi Gurus.

How would I use two cells in an IF statement,

for example IF A1=MON and B2 <1 then B2

 

[pixel]

Hi Gurus.

How would I use two cells in an IF statement,

for example IF A1=MON and B2 <1 then B2

You either nest them in brackets, or you use the AND function.
I prefer the former, as in

=IF(A1="MON",IF(B2<1,B2,0),0)

 

[Trembling Hand]

Thanks Pixel thats what I am after.

 

[bonkerrs]

Need help with an Excel question

My Excel knowledge is very basic. Could someone please advice.

Is there is a way to separate rows by the colours? As attached.

 

[skc]

Re: Need help with an Excel question

My Excel knowledge is very basic. Could someone please advice.

Is there is a way to separate rows by the colours? As attached.

1. Insert a header row above all the data. Call them Code and Company Name (Or Paul and Mary if you so desire).

2. Highlight all the data including the header row, and apply auto filter (Keyboard shortcut Ctrl-D, F, F).

3. Click the drop down arrow and select sort by colour.

This is a very useful method to do temporary sorting of data.

 

[bonkerrs]

Re: Need help with an Excel question

1. Insert a header row above all the data. Call them Code and Company Name (Or Paul and Mary if you so desire).

2. Highlight all the data including the header row, and apply auto filter (Keyboard shortcut Ctrl-D, F, F).

3. Click the drop down arrow and select sort by colour.

This is a very useful method to do temporary sorting of data.

Nice! Thanks.
This sorts out one colour at a time so just have to go through step 3 until all colours are individually sorted.

 

[skc]

Re: Need help with an Excel question

Nice! Thanks.
This sorts out one colour at a time so just have to go through step 3 until all colours are individually sorted.

You might be able to do all the colours at the same time.

https://www.google.com.au/search?q=...&oe=utf-8&gws_rd=cr&ei=mxB5VevZLoiOmwXCjoDoBg

 

[VSntchr]

Have just done a forced upgrade to a new setup - and with that comes an upgrade to Office 2013. I run a data feed (webIress) into excel, but since upgrading I can't seem to get the add-in to show up.

Does anyone else have experience with getting the add-in to work in Excel 2013?
So far it looks like I'll be rolling back to 2010.

 

[pixel]

Have just done a forced upgrade to a new setup - and with that comes an upgrade to Office 2013. I run a data feed (webIress) into excel, but since upgrading I can't seem to get the add-in to show up.

Does anyone else have experience with getting the add-in to work in Excel 2013?
So far it looks like I'll be rolling back to 2010.

I too have been rolling back to 2010.
Many features of the new version aren't backwards compatible. After finding some changes that rendered activation of worksheets within workbook unusable, I simply gave up looking for a solution and reverted to the version that was consistently doing what my (macro) programs told it to do.

Meanwhile, I have verified that 2010 is also still working beautifully under Windows 10. Suits me.

 

[VSntchr]

I too have been rolling back to 2010.
Many features of the new version aren't backwards compatible. After finding some changes that rendered activation of worksheets within workbook unusable, I simply gave up looking for a solution and reverted to the version that was consistently doing what my (macro) programs told it to do.

Meanwhile, I have verified that 2010 is also still working beautifully under Windows 10. Suits me.

So your one of the brave souls that has been an early adopter of 10 pixel!?
I installed it a few weeks ago but reverted back within half an hour as it was so slow that I was having lag between hitting the keyboard and seeing characters appear on the screen - very frustrating. Figured I'd give it another few months to be ironed out. With this new i7 machine though, I can't see any reason why I wouldn't be able to run it smoothly..and now I've upgraded all my relevant drivers to be Windows 10 compatible.

 

[kaerai]

I too have been rolling back to 2010.
After finding some changes that rendered activation of worksheets within workbook unusable, I simply gave up looking for a solution and reverted to the version that was consistently doing what my (macro) programs told it to do.

Pixel, I tend to use a lot of Macro and VBA for when when macros alone will not suffice in Excel and have stayed with 2007. There were some changes in and after Office 2010 that have caused macros and VBA code created in 2007 and 2010 to not work in the newer versions. It is going to be a very painstaking time when I am forced to start using a newer version of Excel full time.

I installed it a few weeks ago but reverted back within half an hour as it was so slow that I was having lag between hitting the keyboard and seeing characters appear on the screen - very frustrating.

Windows 10 is working fine but unfortunately, sometimes with updates machines may become less responsive and there can be a few reasons for that. It is certainly no comfort for users like you who have that one machine causing the issues.

If you do try again, you might like to try to do a disk cleanup, Scandisk, a Defrag after install. These tools can be found by going to Computer and right clicking on the operating system drive (usually C) and going to properties.
People may think Defrag is not needed on spinning hard drives these days, but it still does need to be done. You do not need to defrag an SSD.

Certain motherboard settings can cause issues and the casual user will not know or want to have to mess with them if they were not initially set up correctly when the PC was built.

Also, running the Windows repair in your case may have solved the problem.

 

[CanOz]

I have some statistical analysis that i need done on a few markets. Is there anyone that feels they have the Excel skills to do this if i provide the data?

Of course i would be assuming there would be an hourly rate for this work.

Please PM if you can back-test basic strategies or compile statistics using Excel.

Cheers,


CanOz

 

[Gringotts Bank]

I have some statistical analysis that i need done on a few markets. Is there anyone that feels they have the Excel skills to do this if i provide the data?

Of course i would be assuming there would be an hourly rate for this work.

Please PM if you can back-test basic strategies or compile statistics using Excel.

Cheers,


CanOz

Hi Can,

Price series data, or more like economic data and ratios etc?

 

[CanOz]

Hi Can,

Price series data, or more like economic data and ratios etc?

All price series data...

I've actually found an eLancer. There is another forum where they share stats on thier markets, a bit of a combined quantitative study thread...very helpful. I'd be willing to contribute what i pay to have done if other commit to doing the same.


CanOz

 

[Gringotts Bank]

All price series data...

I've actually found an eLancer. There is another forum where they share stats on thier markets, a bit of a combined quantitative study thread...very helpful. I'd be willing to contribute what i pay to have done if other commit to doing the same.


CanOz

Well if it's something simple, I can have a look. No payment needed.

If it's something you feel very confident about in terms of profitability, try to break it into chunks so that whomever you get to help you, you're not giving away the farm. If you know what I mean? Edges are hard to come by, so you can even get different people to code up different aspects, then combine them yourself.