Excel spreadsheet code help

[Family_Guy]

Just trying to work out how to do a code for working out an issue.

OK.....bare with me, i hope this makes sense.......here is a hypothetical

Lets say i buy 1000 +++ shares at $1
Lets say it drops to .70 a month later
Lets say i buy 1000 more +++ at .70

So now i have
1000 x $1
1000 x .7
ave .85

OK......so the SP hits .80 and hangs there for a while and while i've made a small profit on the second lot, i still haven't made my ave back. I see an opportunity somewhere else and want to sell these +++. I don't want to make a realized loss so i sell the second 1000 and some of the 1st 1000

Trading with a company that charges $15 in/out, i sell the second 1000 and make a $70 profit. BUT, what if i want to get more cash?? I can sell a certain amount of my 1st 1000 with the 2nd 1000 and still come out at $0 profit/loss.

With out going thru the BS to work out that i can sell 350 of the first lot (my calculations, i hope thats correct) what would i be coding in my spreadsheet to work that out in a flash?

I currently have a spreadsheet that easily tells me out prices to make small profits and of course my profits/losses made. I just want to somehow start a new one or add this new equation onto it for instances like this.

Cheers in advance.

 

[cuttlefish]

Re: Excel Spreadsheet help / Code

See image below for sample spreadsheet. The "Additional Parcel Size" is highlighted and you can see the formula =(D2*(C2-A2)-E2*2)/(B2-C2).

Your example is coded into it ... absolute no responsibility taken if my maths is dodgy.

 

[Family_Guy]

Excellent....cheers for that

 

[Gringotts Bank]

Hi Excellers,

I want to do the following. Hope someone can help.

IF ISNUMBER(A1), THEN click the hyperlink in A2, AND copy the value of A1 and paste into the hyperlinked page. And make it loop continuously.

I'm trying to automate a system. Thanks.

 

[pixel]

Hi Excellers,

I want to do the following. Hope someone can help.

IF ISNUMBER(A1), THEN click the hyperlink in A2, AND copy the value of A1 and paste into the hyperlinked page. And make it loop continuously.

I'm trying to automate a system. Thanks.

Can you post an example of A1 and A2? And where in the hyperlinked page do you want to paste A1? You may also have to explain the loop condition: Are there B1, B2, and C1, C2... to loop over? Or do you simply want to loop the hyperlink? If so, how is A1 changing?

 

[Gringotts Bank]

Can you post an example of A1 and A2? And where in the hyperlinked page do you want to paste A1? You may also have to explain the loop condition: Are there B1, B2, and C1, C2... to loop over? Or do you simply want to loop the hyperlink? If so, how is A1 changing?

Thanks Pixel.

It's not actually A1 and A2, I was just trying to keep it simple.


So what I want is:

If ISNUMBER(T72), copy P72 (stock name) and paste to the link in V72 (broker). Then do the same with R72 (form fill volume) and T72 (form fill buyprice).

Then I probably need to delete row 72 so that that stock doesn't get sent again. It can continue to loop, checking column T for a number, because if it's blank then nothing will happen.

I just thought of something. I also need to click send on the broker page. Would I be better using Autohotkey for this? If you're working on it please stop. There's another issue and that is if 2 or more stocks show up as buys.

 

[pixel]

Thanks Pixel.

It's not actually A1 and A2, I was just trying to keep it simple.

View attachment 69852
So what I want is:

If ISNUMBER(T72), copy P72 (stock name) and paste to the link in V72 (broker). Then do the same with R72 (form fill volume) and T72 (form fill buyprice).

Then I probably need to delete row 72 so that that stock doesn't get sent again. It can continue to loop, checking cell T72 for a number, because if it's blank then nothing will happen.

I suppose you'll have to do it in a macro.
=select.last.cell()
Last.Row=get.cell(2)
=select("R1C20") ** column 20 being T
=while(get.cell(2)<=Last.Row)
=select("RC20")
T.Value=get.cell(5)
=if(isnumber(T.Value))
=select("RC16")
P.Value=get.cell(5)
=select("RC18")
R.Value=get.cell(5)

insert the formulas here what you want to do with P, R, and T values
do you want to append them to the URL?
or read the URL and insert them somewhere into the website?
As I don't have access to cmcmarkets, I can't tell where the data go and how you place the order
if you've read the URL, don't forget to Close it
or else you need to mess with get.document(78) or whatever

=end.if() ** closes if T.value is a number
=select("R[1]C20")
=next() ** ends the loop

PS: If you delete the row after placing the order, don't forget to decrease the value of Last.Row and select the R[-1], so you don't miss the one below the one you just deleted.

 

[Gringotts Bank]

I suppose you'll have to do it in a macro.
=select.last.cell()
Last.Row=get.cell(2)
=select("R1C20") ** column 20 being T
=while(get.cell(2)<=Last.Row)
=select("RC20")
T.Value=get.cell(5)
=if(isnumber(T.Value))
=select("RC16")
P.Value=get.cell(5)
=select("RC18")
R.Value=get.cell(5)

insert the formulas here what you want to do with P, R, and T values
do you want to append them to the URL?
or read the URL and insert them somewhere into the website?
As I don't have access to cmcmarkets, I can't tell where the data go and how you place the order
if you've read the URL, don't forget to Close it
or else you need to mess with get.document(78) or whatever

=end.if() ** closes if T.value is a number
=select("R[1]C20")
=next() ** ends the loop

PS: If you delete the row after placing the order, don't forget to decrease the value of Last.Row and select the R[-1], so you don't miss the one below the one you just deleted.

Thanks very much for your help.

...or read the URL and insert them somewhere into the website?

Yes, insert them into the URL that opens (CMCmarkets order page).

I'm at a really basic level with Excel so I'm going to need to digest what you've done before I ask any further questions about it. Does ** indicate comments outside of the code?

Sometimes 2 or more orders might show up, so I have to think about that too.

 

[pixel]

Does ** indicate comments outside of the code?

yes, I usually put comments in the next column of the macro, but couldn't do so here.

As it's easier to read, I use R1C1 reference style, which can be set in Excel Options under the Formulas tab.
How do you get the P, R, and T columns filled? Especially WHEN is the time that T receives a numerical value?

 

[Gringotts Bank]

yes, I usually put comments in the next column of the macro, but couldn't do so here.

As it's easier to read, I use R1C1 reference style, which can be set in Excel Options under the Formulas tab.
How do you get the P, R, and T columns filled? Especially WHEN is the time that T receives a numerical value?

Uh, you'll laugh at this. I'm planning to use Autohotkey as follows. Amibroker --> notepad++ for quite a bit of manipulation using a macro ---> Excel which references about 8 conditions from earlier calcs in prior columns--> broker.

It results in 1600 stocks being filtered down to usually 0-8 stocks. T gets filled when the conditions are all true from prior columns. Otherwise it's blank.

The other way to do it would be to use webiress and request approx 1600 stocks directly into Excel, which I'm told can be a slow process, plus I miss the more simple filtering of Amibroker and notepad.

 

[pixel]

request approx 1600 stocks directly into Excel,

At last count, the number is 2032.
And yes, reading that many records into Excel is taking time, especially with the newer versions, e.g. Office 360. But grab a copy of the good old Excel 97 and it's quite feasible. I get the latest prices (20 minutes delayed, free) into my Dividend forecast every day. ... about two codes a second

 

[Gringotts Bank]

Would you mind commenting the other lines of the macro please? I think I'm going to need that.

I'm sticking to <$2 stocks, which works out about 1600.

 

[pixel]

Would you mind commenting the other lines of the macro please? I think I'm going to need that.

I'm sticking to <$2 stocks, which works out about 1600.

the language is XLM (Excel Macro, predecessor of VBA)
'select' is the equivalent to putting the cursor on the referenced cell
R1C20 references first row, 20th column, i.e. T20 in A1 ref style
if I leave the number off, as in RC18, the row remains unchanged.
get.cell(2) returns the row number of the currently selected cell
get.cell(5) returns the contents
T.Value=get.cell(5) has therefore the effect of putting the contents of the current cell into a variable named "T.Value", etc; so every statement between
=if(isnumber(T.Value) and the corresponding =end.if() will be executed if the current row has a numeric value in column 20 (=T)
Similarly, =while(get.cell(2)<=Last.Row) opens a loop from the current row (started at row 1) and runs until the last row. Loops are closed by a =next() statement, which in our example better be preceded by selecting the next row as in =select("R[1]C20").
no more to it

 

[Gringotts Bank]

the language is XLM (Excel Macro, predecessor of VBA)
'select' is the equivalent to putting the cursor on the referenced cell
R1C20 references first row, 20th column, i.e. T20 in A1 ref style
if I leave the number off, as in RC18, the row remains unchanged.
get.cell(2) returns the row number of the currently selected cell
get.cell(5) returns the contents
T.Value=get.cell(5) has therefore the effect of putting the contents of the current cell into a variable named "T.Value", etc; so every statement between
=if(isnumber(T.Value) and the corresponding =end.if() will be executed if the current row has a numeric value in column 20 (=T)
Similarly, =while(get.cell(2)<=Last.Row) opens a loop from the current row (started at row 1) and runs until the last row. Loops are closed by a =next() statement, which in our example better be preceded by selecting the next row as in =select("R[1]C20").
no more to it

My head is spinning slightly. Probably just need to keep at it til it feels natural. Great to get some expert help so quickly. Thanks again.

 

[pixel]

hmmm - I'm not familiar with Amibroker and its hot keys into Notepad, so on that count, I'm about on the same level as you with Excel Macros.
I can understand that you wish to keep your 8 conditions off the Open Forum.
Do you fill them once in the morning before Market Open? How often would you refresh the criteria? And could the lower columns be filled inside the same Excel Macro?
It does seem to be a round-about way to use three (including CMCmarkets, even 4) systems for a rather simple task. It ought to be possible to program everything in Excel if it can be done with Notepad. But then again, I've been writing all kinds of XLM programs for close to 25 years.

 

[Gringotts Bank]

hmmm - I'm not familiar with Amibroker and its hot keys into Notepad, so on that count, I'm about on the same level as you with Excel Macros.
I can understand that you wish to keep your 8 conditions off the Open Forum.
Do you fill them once in the morning before Market Open? How often would you refresh the criteria? And could the lower columns be filled inside the same Excel Macro?
It does seem to be a round-about way to use three (including CMCmarkets, even 4) systems for a rather simple task. It ought to be possible to program everything in Excel if it can be done with Notepad. But then again, I've been writing all kinds of XLM programs for close to 25 years.

I'm aiming to re-fill every minute or so until about 1pm.

It's actually Notepad++. Not sure if you are familiar with it, but it has awesome filtering capabilities via plug insn (Linefilter2). It gets my formatting perfect to insert into excel. Very easy to use.

AB -> notepad++ -> excel calc should theoretically take < 5sec. Excel could take forever with webiress.

The big problem is that depth is one of the things I want to filter pretty heavily with quite a few calcs. ASX depth is very hard to access programatically.

 

[pixel]

okay,
no, I'm not familiar with Notepad++ either.
When I was in the business of writing scripts, it was for the Market Analyser, using Pascal, with which I've been familiar even longer than Excel. Some of those criteria I have converted into Excel, but for real-time decisions, alerts based on Pascal scripts using Iress real-time data, were my only tools. MA 7 offers the facility to use the same scripts for market scans, live charting, and alerts.