Microsoft Excel Q&A

wabbit · 9 March 2009

MS+T

Personally, I'd have a look at writing custom functions for this, maybe something like:

=LastNonZeroInRange(A:A)

where LastNonZeroInRange would be a function like:

Function LastNonZeroInRange(rng As Range) As Integer

'counting from the bottom up, find the first non-zero value
Dim r, c As Integer

r = 65536
c = rng.Column

Do Until Cells(r, c).Value <> 0 And Not (IsNull(Cells(r, c)))
r = r - 1
Loop

LastNonZeroInRange = Cells(r, c).Value

End Function

but then again, that's just what I'd do

Hope this helps.

wabbit

MS+Tradesim · 9 March 2009

Brilliant. That works beautifully. Thank you very much wabbit. Please accept an e-beer. :bier:

wabbit · 9 March 2009

The question that needs answering: how many e-beers in a real one?

wabbit

MS+Tradesim · 9 March 2009

If you're ever on the Sunshine Coast of QLD, let me know, and I'll buy you a real one, or two, or...

michael_selway · 9 March 2009

wabbit said:
The question that needs answering: how many e-beers in a real one?

wabbit

MS+Tradesim said:

Question:

I have a column of data that contains 1, -1 or 0. A new value is added to the bottom of the list every X seconds/minutes. Say the column populates A1:Axxx

At the moment I am using this to return the value of the lowest populated cell in the list:
=INDEX(Sheet2!A1:A1,MATCH(9.99999999999999E+307,Sheet2!A1:A1))

Say the list is:

1
0
0
-1
0
1
0
-1

Then the formula returns "-1". However, what I would like to do is have the formula return the value only if <> 0, and if = 0 then leave the previous result.

So in the above example say that after the last -1, two more zeros are added, then I want it to keep returning the bottom most value that is not 0.

Alternatively, I would like a formula to copy all the values <> 0 into a new column.

Thanks in advance for any help, and in the meantime I'll keep looking for a solution.

Click to expand...

Another question I have is when do we ever need to use the above (0,1,1,-1 etc)!

Just wondering....

thx

MS

kotim · 9 March 2009

FOLKS A QUERY wiTH PERCENTAGE OCCURENCES.

If I have a column of numbers, in this instance each number represents a points range and what I want to do is be able to sort this column into percentage occurences. By this I mean I my highest number is 200 and my lowest is say 50 and there are various numbers in between, what i want to do is have excell sort the list into percentage occurences.

Ie I want to have the ranges 50-70, 71-90, 91-110 and so forth and then have excell pull the numbers from my original colum and drop them into the the reference ranges above ( 50-70 etc) and then be able to graph the odds of each of those reference ranges. That way I can see how often a market moves a certain distance before turning around

What I am doing is using gann swing ranges and I want to see how often certain point ranges occur so that I can use profit targets, but more importantly use this information for inital stops.

thanks

wabbit · 9 March 2009

Have a look in the help files for "histogram" under the data analysis toolbox, or the "frequency" worksheet function.

Hope this helps.

wabbit

MS+Tradesim · 9 March 2009

michael_selway said:
Another question I have is when do we ever need to use the above (0,1,1,-1 etc)!

Just wondering....

thx

MS

Well, you can use Excel for nearly anything. In this case, I use Metastock to send signals into a spreadsheet which is then part of my auto trading program.

1 = Long
-1 = Short
0 = no signal

doctorj · 10 March 2009

Any idea how to make charts like this in excel?

Thanks in advance.

wabbit · 10 March 2009

Give each data point an x and y value, the z value is the data value; then plot as a bubble chart.

e.g.
x y market cap
1 10 1000
1 50 1234
1 100 300

adjust the x,y to make things look nice

Hope this helps

wabbit

doctorj · 10 March 2009

Any idea if it creates the size of the bubbles by radius or area?

wabbit · 10 March 2009

never bothered to check...

plot them and see for yourself... then let us know.

wabbit

doctorj · 10 March 2009

Cheers

wabbit · 10 March 2009

On the bubble chart, select the data series > format data series then size by area OR width.

wabbit

Trembling Hand · 1 April 2009

Guys how do you set the data range for a graph to plot new cells as extra data is added.

I want to graph my P & L and have the graph update without me having to manually increase the Series range every update.

For example currently I am using this,

='March Trades'!$B$10:$B$200

I'm sure there is a way to make the graph plot cell B201 when its got data in it?

skyQuake · 1 April 2009

Excel:

Use OFFSET and COUNTA.
Kinda hard to explain, heres a few links that do it better than me

http://office.microsoft.com/en-us/excel/HA011098011033.aspx
http://www.meadinkent.co.uk/xlgraphoffset.htm

Open Office:
Not possible... unless u write a Macro to do it for you......

helicart · 19 April 2009

Nice to see some sharp coders here. I am an excel hack, with intermediate skills, but am not a programmer (have a biol sciences background)

I've had a couple of issues for a over a year that I haven't been able to resolve, even with help from MrExcel.

Problem 1.

I have a trade tracker that amongst other things, fetches prices from MSN Money. However, the stock symbol string thrown to msn web query is limited by a 255 character limit. This limits the number of stocks that can be included in one query to around 35. Does anyone know how to code a macro to send a string of stocks directly to msn without using iqy web queries.

I had some help with this last year but the result was extremely slow so I stuck with web queries.

File is here and password for macros is 'z' if anyone wants to mess with it.

Problem 2.
Last year I was experimenting with a simulator for position sizing. As many would know, stats used in financial modelling are most often parametric - such as VaR and sharpe ratio.

I was trying to run the random number generator in the monte carlo sim in a manner that generated non parametric distributions i.e. log normal. I am using the right manipulation of the random numbers to generate log normal =LOGINV(RAND(),meanlog,sdlog).

However the plot of derived R values appear like a normal distribution.
If anyone has experience with monte carlo sims using non parametric stats, I would appreciate discussing it with you.

The file is here if you want to see what it tries to do. Because of the monte carlo run, it is best to close all other xls when running it.

Trembling Hand · 15 June 2009

Hi guys I need some help to waste time backtesting something that will not work in practise : but anyway

I have a stock Code column that I need to drop the "A" in the tickers,

A001250
A004060
A001720
A001750
A016610
A016420
A030210
A001290

Any ideas?

el caballo · 15 June 2009

TH,

If I have understood the question correctly, the RIGHT funtion should work. Examples in th elink below:

http://www.techonthenet.com/excel/formulas/right.php

Trembling Hand · 15 June 2009

el caballo said:
TH,

If I have understood the question correctly, the RIGHT funtion should work. Examples in th elink below:

http://www.techonthenet.com/excel/formulas/right.php

Ripper, thats what I wanted. thanx

Microsoft Excel Q&A

wabbit

www.wabbit.com.au

MS+Tradesim

wabbit

www.wabbit.com.au

MS+Tradesim

michael_selway

Coal & Phosphate, thats it!

kotim

wabbit

www.wabbit.com.au

MS+Tradesim

doctorj

Hatchet Moderator

Attachments

wabbit

www.wabbit.com.au

doctorj

Hatchet Moderator

wabbit

www.wabbit.com.au

doctorj

Hatchet Moderator

wabbit

www.wabbit.com.au

Trembling Hand

Can be found on the bid

skyQuake

helicart

Trembling Hand

Can be found on the bid

el caballo

Trembling Hand

Can be found on the bid

Similar threads

Microsoft Excel Q&A

www.wabbit.com.au

www.wabbit.com.au

Coal & Phosphate, thats it!

www.wabbit.com.au

Hatchet Moderator

Attachments

www.wabbit.com.au

Hatchet Moderator

www.wabbit.com.au

Hatchet Moderator

www.wabbit.com.au

Can be found on the bid

Can be found on the bid

Can be found on the bid

Similar threads

Privacy & Transparency

Privacy & Transparency