Loop through hundreds of variables continuously?

[Nelz]

I have a table that's basically a spreadsheet of a year's worth of stock closing prices on 100 stocks. I have a macro that runs a series of about 11 querys to compare any two symblos to each other, each one performing a different computation on the data produced from the previous one. What I need to figure out is if there is a way to query the master table to create a list of all 100 symbols, then plug them into the variable slot one pair at a time until the entire list has been run in every combination (1 to 1 ...1 to 2...1 to 3..etc). The last query in my macro creates a one line result, and what I wanted to do was make that an append query that would append that one line to a table...one line at a time for each pair. After all were run...that final table could be querey'd to see if any two pairs produced the desired result by using one final query with a bunch of IIf statements.

I've gotten it to work on any one pair...but you have to plug in Stock1 and Stock2 on a form, and one of the first querys reads the symbols from there after you click a button to start the macro.

What the client wants is to hook the master table up to a constantly updating excel spreadsheet and just keep running every combination of the 100 stocks over and over again. Can this be done?

 

[AWithers]

What I would do is this

create a recordset of the unique list of symbols

create a loop to loop through this list

then the second level of loop goes through a similar but different recordset of the symbols that dont include the symbol currently in the outer loop

at eachstage of this inner loop append a row into your table

in the end you will end you with the data you want.

Sorry I havent got any code to hand, see the help for use of recordsets

Hope this helps

Andy

 

[LittleSmudge]

If you want to cycle through 100 stocks in field names Stock1, Stock2.. .. StockMax ( Where Max is a big number ) then

Given table name = tblStockData

Private Sub RunCalcs()
Const intMax = 100 ' Or whatever number you have
' open a RecordSet
Dim intI As Integer
Dim Answer(Max,Max) As Array of type depending on your Math Function
Dim rst As New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM tblStockData"

While Not rst.EOF ' This will cycle through Records

For intI = 1 to (intMax-1) ' Cycle through first part of compare
For intJ = intI+1 to intMax ' Cycle through second part of compare
' Here comes the clever bit that addresses you real problem.
YourAnswer(intI,intJ) = rst("Stock" & intI) YourMathFuntion rst("Stock" & intJ)
Next intJ
Next intI

rst.MoveNext
Wend

rst.Close
End Sub


'ope-that guides you in a useful direction.

G LS

 

[Nelz]

OK...I'm seeing a little light...but still confused since I'm not good at VBA coding. Right now the second of 11 quereys is getting the info for Stock1 and Stock2 from an input form...for lack of a better starting point. So under the criterea in that query for Stock one and 2 it says [Forms]![Form1]![Stock 1], and [Forms]![Form1]![Stock 2].

I made a macro that runs when you hit the button that clears all the temporary tables etc., then runst through the series of querys. The last query in the list displays the results I want for the pair of stocks entered.

How would I make it keep running this same macro over and over again, but replacing [Forms]![Form1]![Stock 2] with the next symbol in the table that has the symbols listed...one at a time. I'll call that symbolstable .

 

[raskew]

My first thought would be that if you're going to be working in Access, then you need to convert your spreadsheet approach (which sounds to be something near 100 fields by 250 rows) to a properly normalized table. If interested and you need guidance, post back with your table description.

For an example how you could loop through a normalized table, try building this small sample:

Symbol TDate Price
JKL 5/15/02 $40.23
JKL 5/16/02 $41.45
JKL 5/19/02 $39.60
JKL 5/20/02 $40.15
MNO 5/15/02 $16.25
MNO 5/19/02 $17.60
MNO 5/20/02 $17.70

Name it tblStocks.

Then a second table structure that looks like this:
Symbol1 Text
tDate1 Date/Time
Symbol2 Text
tDate2 Date/Time
theDiff Currency

Name it tblStockScan

Then use the following function to loop through every possibility
and populate tblStockScan with the inspected symbols, their dates and
the difference in price between the two.

Once the function is installed in a new module, from the debug
window type: ? StockScan <enter> and let it rip.

The price difference is just an example of doing something with the data.

Function StockScan()
'*******************************************
'Name:      StockScan (Function)
'Purpose:   Compares every possible combination
'           of Symbol/Date, noting the difference
'           in price of each set of data.
'*******************************************
'
Dim db As DATABASE
Dim rs As Recordset, rs2 As Recordset
Dim rs3 As Recordset
Dim strSQL As String, strSQL2 As String
'
Set db = CurrentDb
'
'strip tblStockScan
strSQL = "DELETE tblStockScan.* " _
        & " FROM tblStockScan;"
docmd.SetWarnings False
db.Execute strSQL
docmd.SetWarnings True
'
Set rs3 = db.OpenRecordset("tblStockScan")
'
strSQL = "SELECT Symbol, TDate, Price" _
    & " FROM tblStocks" _
    & " ORDER BY Symbol, TDate;"
Set rs = db.OpenRecordset(strSQL)
Do Until rs.EOF
   msg = ""
   psym = rs!symbol
   pdate = rs!tdate
   strSQL2 = "SELECT Symbol, TDate, Price" _
    & " FROM tblStocks WHERE ((" _
    & " symbol = '" & psym & "' AND" _
    & " tdate = #" & pdate & "#)= False) ORDER" _
    & " BY Symbol, tDate;"
   Set rs2 = db.OpenRecordset(strSQL2)
   Do Until rs2.EOF
   ' add code to do something
      rs3.AddNew
      rs3!symbol1 = rs!symbol
      rs3!tdate1 = rs!tdate
      rs3!symbol2 = rs2!symbol
      rs3!tdate2 = rs2!tdate
      rs3!theDiff = rs!price - rs2!price
      rs3.Update
      rs2.MoveNext
   Loop
   rs.MoveNext
Loop
rs.Close
rs2.Close
rs3.Close
db.Close
Set db = Nothing
'
docmd.OpenTable "tblStockScan", acViewNormal, acReadOnly
'
End Function

 

[Nelz]

Thanks raskew...I'm sure youre on the right track. The problem is that the formulas of what the client wants to do with the data (cloasing price) is so complex...that the only way I could make it work was to do it one step at a time. The original table structure is simple. The columns are Symbol, Date, Closing Price. The rows just keep repeating 365 rows for each symbol.This table will eventually be hooked up to an external excel spreadsheet that will feed it the current days closing price and delete the oldest one...so there will always be one year of data. I have that part figured out (I think). From there there are a number of steps (11) that take an average of the past 20 days, and 50 days, and 14 days, and then compare two stocks. From there a ratio is created and then the ratio is applied to another formula (that is really bizarre and complicated and that makes no sense, but is the clients own invention) and then the result is either true or false. If its true, the want to know. So I have it all working off a form where you plug in the two symbols you want to compare, and it pulls all the info from that original table and querys it by running all these querys in order via a macro. The final result is on one line. There is no VBA code used at all...just a lot of querys and temp tables.

What I need to do is go back to that table and query it for a list of all the ticker symbols, and plug all combinations into the existing macro. Then I could append the answer line to a table. So if I had 100 symbols, this table would be thousands of rows, but only a small number would meet the criteria, if any. Thats what they need to know.

I thought maybe the macro could loop through all the combinations, plugging them into the variable field of stock1 and stock2. I just dont know enough about programming to figure out how to do that, or even where to call it from.

Does that give a better idea? I'm really not as advanced as you are, but I can see that I'll need to create a recordset somehow, plug it into the two variables somehow, and loop through the routine somehow. I'm over my head, and the client is not happy. Help please?????????? Thanks.