looping through a query field by field..

[jakehey1]

Hey guys,
I have an assignment to do which is as follows: First I am given some stock price data, I made a table for it tha stores the data, but the I want to caclulate the returns of these stock prices(each stock price is in a field, and my ID is the date)..the idea is to use just a nested for loop that goes through each each row for a specific company(field) calculates the return and the moves to the next company and does the same task.. I know that in Excel it is just a command but I am assigned to do it in Access and I simply dont know VBA.. Any help would be strongly appreciated.. Thanks!

[jakehey1]

i dont know but a proper pseudocode should be like that:

go to the specific database
go to this query which is based on the table prices
for i=2 to length(size(query,2)%loop through the columns (i dont care about the dates)
for j=2 to length(size(query,1)&loop through the rows
returns(i,j)=log((prices(i,j)/prices(i-1,j-1)
end
end
show me the modified query

[jakehey1]

Also something else which should be correct but again I dont know how:
First I made just a table with the companies and I passed the unique ID. Then I made the table of the stock prices so it is a time series so it is like date and the fields are the companies. But I would like to say that the name of the fields which is for me 1,2,3 Etc are the IDs from the table companies. I was looking if there is a possibility for a composite key this one that will have the date and the company ID. Guys I appreciate for the help!!

[jforbes]

Fair warning, your post will most likely get removed as this site is more about real world examples than doing someone's homework for them.

But before then, if I were you, I would spend some time in the Query Editor to try to get a good grasp on what it is about. You are looking too closely at the problem and you are trying to write procedural program to do the heavy lifting, while Access Queries are set based. This might help, http://sqlmag.com/t-sql/programming-sql-set-based-way

[jakehey1]

jforbes thanks for the remark..Actuall y I am in an internship so it is not directly homework, but thank you for pointing out. But cant I write a funtion in the VBA editor that takes as an input the table prices and returns the query?
Also I see the recordset command where you ho through each record. But in time series data you need to go through each field. Is there an equivalent fieldset?

[jforbes]

Sure, you can write a function that opens up a RecordSet and loops through it. MSDN has an example of looping a RecordSet: https://msdn.microsoft.com/en-us/lib.../ff820966.aspx

But again, Set based process will probably work better for you in the long run. Especially since it sounds like you are grouping the records by Customer and performing some math on the Group. This is a good example of Set Based Aggregates: https://support.office.com/en-us/art...4-8c8dbbe41c8a

[jakehey1]

well this is exactly my problem.. everyone who uses access they talk about customers orders etc which is cross section data...So the record idea is useful. For me it is not! because at each field I have the companies and I dont care about the record which in my case it is the stock price of the companies at each particular day..

[Rabbit]

It sounds like your data is not normalized, which is why you think you need to use VBA and loop through every row and every column.

But it's most likely not necessary at all. It's very unclear what you are trying to accomplish, because it sounds like you already arrived at what solution you want to implement rather than describing the issue and letting us guide you to the optimal solution.

[jakehey1]

Thanks Rabbit for the reply. Well I am familiar with Matlab for instance, and I know that if I have to calculate some stuff and I have a matrix I have to loop to the matrix etc... Well Matlab is handy when it comes to matrices. Now I really dont know what is optimal really because I dont know what lies behind VBA. I dont know even the difference between SQL and VBA (Sorry for that) so yeah, I just approach the problem based on Matlab

I can describe the problem once more. I created a table with companies that have unique ID and another table of daily stock prices that span 5 years. My total goal is to create an index but this is for later.. Anyhow I want to create a query that maipulates the table of the stock prices. I need to calculate the returns for the stock prices. For instance say stock A closed at t=1 at 20$ and at t=2 is at 30$ then the return is 50%. This is what I want to do basically for all the rows(time) for all the columns(compani es) The thing is that I am uaware of Access VBA.. The employer told me that first I have to set up the database and optimize it and the to create the index. So basically I have to do everything in Access and Excel. And by the way the data matrix is huge so the OPTIMIZATION is important for them.
And of course they told me that when data enter the database so the stock prices for instance are updated, the query should display the results. I just got the job and I try to read as much as possible to understand how to do it and they gave me a deadline for the step 1 which is the database construction so yeah...And the hilarious part is that this was not part of the agreement but yeah I am an intern...

[jakehey1]

guys if you have a table or how do you go through each row? and if you want to relate a certain cell in the field with the previous one how do you do it?

[Rabbit]

It would help if you posted sample data along with results.

[jakehey1]

Hey Rabbit,
Sorry for being a bit late for replying but was trying to get familiar with VBA. VBA looks beautiful and I really want to get better! Now with respect to the project here I have some sample data of 10 companies with their prices downloaded from datastream. I have to calculate the returns in a query. My boss told me that we should insert the data in a proper database because Excel cannot handle such a big bunch of data. So as to do the index they cover more than 250 companies out of which we pick the best according to some financial metrics. Now I am in the step again where I put the raw data (prices) in the database, where I created a table. The second step is to do a query that calculates the returns but as you know I am stuck there. Check out how the data looks like and if you have any recommendations what to do so as to make a query you are welcome!! At first sight if you see the data along each row we have the prices of 10 companies for a specific date. And as I told you it is really weird because I should specify the key and I don't know whether the companies ID is my best option since it is a time series.Assume that the keys is 1 up until 10! Really if you can propose and guide me it would be great. Learning from pros would make my learning curve steeper and my stress will go away hopefully. Thank you again all you guys for possible recommendations '! The results are next to the prices! it is actually the logarithm of the prices of the next divided by the logarithm of the prices of the previous day basically!

[jakehey1]

Hey guys,
I have a question any hint would be greatly appreciated. I set up a table with the stock prices of my companies and then I did a query that takes the fields date prices and now I am up to calculating the returns. My code though does not work: I did the following: I tried to go through each cell of the field prices and then I calculated the differences of the logarithms of the prices but then my result I tried to append it as a new field in the query and it told me that the query is not updatable. So I try to overcome the problem by coding a function that takes as an input a field from my query and the output is a new field with the returns of the stock prices! then I can just append the field to my query. Is it wise or not? I dont have experience in Access so I am not sure if that would be correct. Any help would be greatly appreciated! thank you for your time and assistance!

[jakehey1]

Here is the code that i try to work on it. However it says that there is a run time error 3219 because there is an invalid operation in the query. I try to put the result in the newly created field of my query. Here is the code hope it helps to get what I mean

[jakehey1]

Public Sub lists()

    CalcReturns



End Sub

Public Function CalcReturns(Optional RequiredFld As Field) As Field
Dim db As DAO.Database

Dim qdf As DAO.QueryDef 'use this for a query
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim temp As Double
Dim result As Double
Dim temp2 As Double
Set db = CurrentDb
Set qdf = db.QueryDefs("ReturnsQry")
Set rs = qdf.OpenRecordset
Set RequiredFld = db.QueryDefs("ReturnsQry").Fields("CompanyPrice")
Set CalcReturns = db.TableDefs("tblPrices").Fields("CompanyReturns")

With rs

Do Until rs.EOF

For Each fld In rs.Fields
Debug.Print fld.Name

If fld.Name = "CompanyPrice" Then
temp = fld.Value
rs.MoveNext
temp2 = fld.Value
.AddNew
CalcReturns.Value = Log(temp2 / temp)
.Update
Debug.Print CalcReturns.Value
rs.MoveNext
DoCmd.OpenQuery "ReturnsQry"
End If
Next

Loop

End With
rs.Close
db.Close
DoCmd.OpenQuery "ReturnsQry"
Set rs = Nothing
Set fld = Nothing
Set qdf = Nothing

Set db = Nothing
End Function