SAVE SELECTED RESULTS WITH LATCHING

  • Thread starter Thread starter N. Spokes
  • Start date Start date
N

N. Spokes

I put a number from 1 to 200 in A1. Excel then takes this number and, using
a 2-column look up table in array BA1:BB200, the applicable stock symbol in
BB1:BB200 is inserted in E1 and the stock's daily moves are tested for fit
to certain criteria that are determined by a complex spreadsheet in E1: AZ
200. I get four outputs like "Squeeze" "Buy alert", "Strong Buy", "Sell
alert" and so on, variously, in A2:D2. For symbol #1 I can
straightforwardly make the output appear in cells BE1:BH1 but I want to
latch them there and automatically go on to the next symbol which is in cell
BB2 and create ouputs for this next symbol in row 2 in cells BE2:BH2 and so
on.

Two questions
1/ How can I set the system running so that the spreadsheet steps through
each stock symbol/number upon completion of the spreadsheet calculation
(about 1 second).
2/ How do I latch the outputs in successively lower rows as the input steps
through the 200 symbols?

I can see accomplishing the objective with 200 worksheets, but since each
worksheet is MB in size and loaded with complex formulae, this gets very
unwieldy.

I have put together a spinner to step through the numbers. I have no skills
with macros, but I suppose I could use a macro to manually take the output
to progressively lower rows with <paste special> on each trip but how do I
do the stepping process automatically?

The NOW() function seems to be apt for timing but how do I get it to operate
so as to recognize when a spreadsheet calculation is complete and the system
is ready to go to the next calculation? My first introduction to the NOW()
function was today and I don't see how to get seconds out of it, only
minutes.

Thanks for any suggestions,
Neil.

[Unfortunately, there's no performance proof on finding winners in the
market yet - still in the research stage!]
 
Hi
you may try the following code which does the following:
1. Inserts each value of your stock range in BB1:BB200 into A1
2. Let Excel calculate the results
3. Copies content of A1 as well as BE1:BH1 to the rows below (I wasn't
sure whta your range A2:D2 stores??)
Paste this macro in one of your workbook modules - invoke it on your
worksheet (you may have to adapt some of the ranges

------
Sub latch_value()
Dim rng_stocks As Range
Dim cell As Range
Dim rng_calculate As Range
Dim row_count As Integer
Dim col_stocks As Integer
Dim row_stocks As Integer
Dim wks As Worksheet
Dim copy_range As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wks = ActiveSheet
Set rng_stocks = wks.Range("BB1:BB200")
col_stocks = rng_stocks.Column
row_stocks = rng_stocks.row
Set rng_calculate = wks.Range("A1")
Set copy_range = wks.Range("BE1:BH1")

For row_count = row_stocks To (rng_stocks.row + rng_stocks.Rows.Count -
1)
If wks.Cells(row_count, col_stocks).Value <> "" Then
rng_calculate.Value = wks.Cells(row_count, col_stocks).Value
wks.Calculate
copy_range.Copy
copy_range.Offset(row_count - row_stocks + 1, 0).PasteSpecial
Paste:=xlPasteValues
rng_calculate.Copy
rng_calculate.Offset(row_count - row_stocks + 1,
0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

-----

If you have further questions just come back to this NG :-)


--
Regards
Frank Kabel
Frankfurt, Germany

N. Spokes said:
I put a number from 1 to 200 in A1. Excel then takes this number
and, using a 2-column look up table in array BA1:BB200, the
applicable stock symbol in BB1:BB200 is inserted in E1 and the
stock's daily moves are tested for fit to certain criteria that are
determined by a complex spreadsheet in E1: AZ 200. I get four
outputs like "Squeeze" "Buy alert", "Strong Buy", "Sell alert" and so
on, variously, in A2:D2. For symbol #1 I can straightforwardly make
the output appear in cells BE1:BH1 but I want to latch them there and
automatically go on to the next symbol which is in cell BB2 and
create ouputs for this next symbol in row 2 in cells BE2:BH2 and so
on.

Two questions
1/ How can I set the system running so that the spreadsheet steps
through each stock symbol/number upon completion of the spreadsheet
calculation (about 1 second).
2/ How do I latch the outputs in successively lower rows as the input
steps through the 200 symbols?

I can see accomplishing the objective with 200 worksheets, but since
each worksheet is MB in size and loaded with complex formulae, this
gets very unwieldy.

I have put together a spinner to step through the numbers. I have no
skills with macros, but I suppose I could use a macro to manually
take the output to progressively lower rows with <paste special> on
each trip but how do I do the stepping process automatically?

The NOW() function seems to be apt for timing but how do I get it to
operate so as to recognize when a spreadsheet calculation is complete
and the system is ready to go to the next calculation? My first
introduction to the NOW() function was today and I don't see how to
get seconds out of it, only minutes.

Thanks for any suggestions,
Neil.

[Unfortunately, there's no performance proof on finding winners in the
market yet - still in the research stage!]
Click to expand...
 
Hi
find below a repost of the code to take care of some linebreaks:
----
Sub latch_value()
Dim rng_stocks As Range
Dim cell As Range
Dim rng_calculate As Range
Dim row_count As Integer
Dim col_stocks As Integer
Dim row_stocks As Integer
Dim wks As Worksheet
Dim copy_range As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wks = ActiveSheet
Set rng_stocks = wks.Range("BB1:BB200")
col_stocks = rng_stocks.Column
row_stocks = rng_stocks.row
Set rng_calculate = wks.Range("A1")
Set copy_range = wks.Range("BE1:BH1")

For row_count = row_stocks To (rng_stocks.row + _
rng_stocks.Rows.Count - 1)
If wks.Cells(row_count, col_stocks).Value <> "" Then
rng_calculate.Value = wks.Cells(row_count, col_stocks).Value
wks.Calculate
copy_range.Copy
copy_range.Offset(row_count - row_stocks + 1, 0).PasteSpecial _
Paste:=xlPasteValues
rng_calculate.Copy
rng_calculate.Offset(row_count - row_stocks + 1, _
0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next



--
Regards
Frank Kabel
Frankfurt, Germany

Frank said:
Hi
you may try the following code which does the following:
1. Inserts each value of your stock range in BB1:BB200 into A1
2. Let Excel calculate the results
3. Copies content of A1 as well as BE1:BH1 to the rows below (I wasn't
sure whta your range A2:D2 stores??)
Paste this macro in one of your workbook modules - invoke it on your
worksheet (you may have to adapt some of the ranges

------
Sub latch_value()
Dim rng_stocks As Range
Dim cell As Range
Dim rng_calculate As Range
Dim row_count As Integer
Dim col_stocks As Integer
Dim row_stocks As Integer
Dim wks As Worksheet
Dim copy_range As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wks = ActiveSheet
Set rng_stocks = wks.Range("BB1:BB200")
col_stocks = rng_stocks.Column
row_stocks = rng_stocks.row
Set rng_calculate = wks.Range("A1")
Set copy_range = wks.Range("BE1:BH1")

For row_count = row_stocks To (rng_stocks.row + rng_stocks.Rows.Count
- 1)
If wks.Cells(row_count, col_stocks).Value <> "" Then
rng_calculate.Value = wks.Cells(row_count, col_stocks).Value
wks.Calculate
copy_range.Copy
copy_range.Offset(row_count - row_stocks + 1, 0).PasteSpecial
Paste:=xlPasteValues
rng_calculate.Copy
rng_calculate.Offset(row_count - row_stocks + 1,
0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

-----

If you have further questions just come back to this NG :-)



N. Spokes said:
I put a number from 1 to 200 in A1. Excel then takes this number
and, using a 2-column look up table in array BA1:BB200, the
applicable stock symbol in BB1:BB200 is inserted in E1 and the
stock's daily moves are tested for fit to certain criteria that are
determined by a complex spreadsheet in E1: AZ 200. I get four
outputs like "Squeeze" "Buy alert", "Strong Buy", "Sell alert" and so
on, variously, in A2:D2. For symbol #1 I can straightforwardly make
the output appear in cells BE1:BH1 but I want to latch them there and
automatically go on to the next symbol which is in cell BB2 and
create ouputs for this next symbol in row 2 in cells BE2:BH2 and so
on.

Two questions
1/ How can I set the system running so that the spreadsheet steps
through each stock symbol/number upon completion of the spreadsheet
calculation (about 1 second).
2/ How do I latch the outputs in successively lower rows as the input
steps through the 200 symbols?

I can see accomplishing the objective with 200 worksheets, but since
each worksheet is MB in size and loaded with complex formulae, this
gets very unwieldy.

I have put together a spinner to step through the numbers. I have no
skills with macros, but I suppose I could use a macro to manually
take the output to progressively lower rows with <paste special> on
each trip but how do I do the stepping process automatically?

The NOW() function seems to be apt for timing but how do I get it to
operate so as to recognize when a spreadsheet calculation is complete
and the system is ready to go to the next calculation? My first
introduction to the NOW() function was today and I don't see how to
get seconds out of it, only minutes.

Thanks for any suggestions,
Neil.

[Unfortunately, there's no performance proof on finding winners in
the market yet - still in the research stage!]
Click to expand...
Click to expand...
 
Thanks very much, Frank. I'll need to study VB and your code a bit more
before I can use your ideas which were most kindly put forth. Thanks also
for the followup note. I'll keep you informed of progress and will likely
be back with questions which you kindly offered to reply to.

Best, Neil.



Hi find below a repost of
the code to take care of some linebreaks:
Sub latch_value()
Dim rng_stocks As Range
Dim cell As Range
Dim rng_calculate As Range
Dim row_count As Integer
Dim col_stocks As Integer
Application.ScreenUpdating = False
Dim row_stocks As Integer
Dim wks As Worksheet
Dim copy_range As Range

Application.Calculation = xlCalculationManual

Set wks = ActiveSheet
Set rng_stocks = wks.Range("BB1:BB200")
col_stocks = rng_stocks.Column
row_stocks = rng_stocks.row
Set rng_calculate = wks.Range("A1")
Set copy_range = wks.Range("BE1:BH1")

For row_count = row_stocks To (rng_stocks.row + _
rng_stocks.Rows.Count - 1)
If wks.Cells(row_count, col_stocks).Value <> "" Then
rng_calculate.Value = wks.Cells(row_count, col_stocks).Value
wks.Calculate
copy_range.Copy
copy_range.Offset(row_count - row_stocks + 1, 0).PasteSpecial _
Paste:=xlPasteValues
rng_calculate.Copy
rng_calculate.Offset(row_count - row_stocks + 1, _
0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
 
I created a simple version of the code to try out. I created an excel
worksheet with four formulae in cells B2:E2 referencing cell A2 as the input
cell and a stock list was put in cells H2:H17. The input cell was A2. An
add-in was used in the formulas to abstract the data from the database. I
appeared to find it necessary to change the Set copy_range as follows:
Set copy_range = wks.Range("B$2:E$2") - i.e. a $ sign was needed to
stop simple iteration of line one data only.

All very well! Excellent results and that's great! I noted that the last
input was left behind in cells A2:E2. That's OK. See below (hope it's not
too scrambled by the posting routine).

However, I noted that after running the macro, the whole workbook is
corrupted in that I can no longer copy and paste formulae normally in this
workbook. If I add a new worksheet and use the same add-in I get wrong
answers when copying and pasting in the new worksheet. It's as though the
formulae that I'm using in the add-in disregard the newly typed reference
cell and always use the cell A2 no matter what.

Frank, do you have any thoughts on this? Revised code follows below. [I
added the end Sub line also. ]

Thanks very much,
Neil.

Sub latch_value()
Dim rng_stocks As Range
Dim cell As Range
Dim rng_calculate As Range
Dim row_count As Integer
Dim col_stocks As Integer
Application.ScreenUpdating = False
Dim row_stocks As Integer
Dim wks As Worksheet
Dim copy_range As Range

Application.Calculation = xlCalculationManual

Set wks = ActiveSheet
Set rng_stocks = wks.Range("H2:H15")
col_stocks = rng_stocks.Column
row_stocks = rng_stocks.row
Set rng_calculate = wks.Range("A2")
Set copy_range = wks.Range("B$2:E$2")

For row_count = row_stocks To (rng_stocks.row + _
rng_stocks.Rows.Count - 1)
If wks.Cells(row_count, col_stocks).Value <> "" Then
rng_calculate.Value = wks.Cells(row_count, col_stocks).Value
wks.Calculate
copy_range.Copy
copy_range.Offset(row_count - row_stocks + 1, 0).PasteSpecial _
Paste:=xlPasteValues
rng_calculate.Copy
rng_calculate.Offset(row_count - row_stocks + 1, _
0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next row_count
End Sub
||||||||||||||||||||
Result (roughly):

OPEN(0) HIGH(0) LOW(0) CLOSE(0) EQUITY
INTC 30.03 30.03 28.65 29 A
A 34.92 34.932 33.37 33.72 AAPL
AAPL 22.45 22.46 21.8899 22.19 ADBE
ADBE 37.96 38.17 36.22 36.6 ALTR
ALTR 21.9 22.13 21.31 21.84 AMAT
AMAT 21.87 21.88 20.95 21.21 AMGN
AMGN 63.57 63.73 63.02 63.12 AMZN
AMZN 45.19 45.253 43.35 43.969 twx
twx 17.68 17.7 17.49 17.52 CD
CD 22.53 22.55 22.15 22.27 CE
CE 14.45 14.62 14.38 14.43 CHKP
CHKP 23.07 23.09 21.9 22.18 CMCSK
CMCSK 29.43 29.44 28.58 28.7 CSCO
CSCO 23.17 23.26 22.54 22.75 DELL
DELL 34.02 34.09 33.2 33.36 ELX
ELX 25.02 25.02 22.73 23.4 GPS
GPS 20.52 20.63 20.44 20.45 HD
HD 35.79 35.9 35.16 35.38 HPQ
HPQ 23.35 23.35 22.69 22.91 INTC
INTC 30.03 30.03 28.65 29
 
Hi
you forgot the last two lines. add the following just above 'end sub':
....
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

This should help :-)

--
Regards
Frank Kabel
Frankfurt, Germany

N. Spokes said:
I created a simple version of the code to try out. I created an excel
worksheet with four formulae in cells B2:E2 referencing cell A2 as
the input cell and a stock list was put in cells H2:H17. The input
cell was A2. An add-in was used in the formulas to abstract the
data from the database. I appeared to find it necessary to change
the Set copy_range as follows:
Set copy_range = wks.Range("B$2:E$2") - i.e. a $ sign was needed to
stop simple iteration of line one data only.

All very well! Excellent results and that's great! I noted that the
last input was left behind in cells A2:E2. That's OK. See below
(hope it's not too scrambled by the posting routine).

However, I noted that after running the macro, the whole workbook is
corrupted in that I can no longer copy and paste formulae normally in
this workbook. If I add a new worksheet and use the same add-in I
get wrong answers when copying and pasting in the new worksheet.
It's as though the formulae that I'm using in the add-in disregard
the newly typed reference cell and always use the cell A2 no matter
what.

Frank, do you have any thoughts on this? Revised code follows below.
[I added the end Sub line also. ]

Thanks very much,
Neil.

Sub latch_value()
Dim rng_stocks As Range
Dim cell As Range
Dim rng_calculate As Range
Dim row_count As Integer
Dim col_stocks As Integer
Application.ScreenUpdating = False
Dim row_stocks As Integer
Dim wks As Worksheet
Dim copy_range As Range

Application.Calculation = xlCalculationManual

Set wks = ActiveSheet
Set rng_stocks = wks.Range("H2:H15")
col_stocks = rng_stocks.Column
row_stocks = rng_stocks.row
Set rng_calculate = wks.Range("A2")
Set copy_range = wks.Range("B$2:E$2")

For row_count = row_stocks To (rng_stocks.row + _
rng_stocks.Rows.Count - 1)
If wks.Cells(row_count, col_stocks).Value <> "" Then
rng_calculate.Value = wks.Cells(row_count, col_stocks).Value
wks.Calculate
copy_range.Copy
copy_range.Offset(row_count - row_stocks + 1, 0).PasteSpecial
_ Paste:=xlPasteValues
rng_calculate.Copy
rng_calculate.Offset(row_count - row_stocks + 1, _
0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next row_count
End Sub
||||||||||||||||||||
Result (roughly):

OPEN(0) HIGH(0) LOW(0) CLOSE(0) EQUITY
INTC 30.03 30.03 28.65 29 A
A 34.92 34.932 33.37 33.72 AAPL
AAPL 22.45 22.46 21.8899 22.19 ADBE
ADBE 37.96 38.17 36.22 36.6 ALTR
ALTR 21.9 22.13 21.31 21.84 AMAT
AMAT 21.87 21.88 20.95 21.21 AMGN
AMGN 63.57 63.73 63.02 63.12 AMZN
AMZN 45.19 45.253 43.35 43.969 twx
twx 17.68 17.7 17.49 17.52 CD
CD 22.53 22.55 22.15 22.27 CE
CE 14.45 14.62 14.38 14.43 CHKP
CHKP 23.07 23.09 21.9 22.18 CMCSK
CMCSK 29.43 29.44 28.58 28.7 CSCO
CSCO 23.17 23.26 22.54 22.75 DELL
DELL 34.02 34.09 33.2 33.36 ELX
ELX 25.02 25.02 22.73 23.4 GPS
GPS 20.52 20.63 20.44 20.45 HD
HD 35.79 35.9 35.16 35.38 HPQ
HPQ 23.35 23.35 22.69 22.91 INTC
INTC 30.03 30.03 28.65 29
Click to expand...
 
You're right, Frank - I inadvertently omitted two rows in my initial copy
and pasting - thanks for your patience. The new macro works like a charm
and resolves issues that I mentioned. I gotta learn this stuff, it's very
powerful.

Best,
Neil Spokes (: -))
 
Back
Top