Microsoft Excel Q&A

[wabbit]

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]

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

 

[wabbit]

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


wabbit

 

[MS+Tradesim]

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]

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


wabbit

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.

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

Just wondering....

thx

MS

 

[kotim]

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]

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]

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]

Any idea how to make charts like this in excel?

Thanks in advance.

 

[wabbit]

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]

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

 

[wabbit]

never bothered to check...

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



wabbit

 

[doctorj]

Cheers

 

[wabbit]

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


wabbit

 

[Trembling Hand]

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]

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]

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]

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]

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]

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