VBA Best Practice, Create New Calculated Column

ilcaa

Well-known Member
I have a table called quotes, it has 3 columns, qDate, qTicker, qClose. this table contains 2 million records. I want to create a new column in this table (5DayReturn) that calculates a value based on a record set. here is an example of the table

qDate, qTicker, qCloseCLOSE, 5DayReturn
5/15/2014, GOOG, 265.25,
5/14/2014, GOOG, 268.75,
5/13/2014, GOOG, 262,50,
5/12/2014, GOOG, 265.00,
5/11/2014, GOOG, 263,90,
5/10/2014, GOOG, 259.00,

i want to loop each row and take the current qClose and the next 5 qClose to calculate a return: (qClose(5)-qClose(0))/qClose(0).

My plan is to loop each row, use the current rows qDate qClose, calculate the value then place it in the row, ill do this for each 2 million rows.

Is this the best way to accomplish this task? Any advise on code or best way to do this?

here is the SQL select query
Code:
``SELECT TOP 6 qClose, qDate FROM Quotes WHERE qTicker = rs.Fields("qClose") and qDate >= rs.Fields("qDate") ORDER BY qDate;``

Code:
``````Sub CalculateReturn

Dim rs as RecordSet
Dim i as Long
Dim db as Database

Set db = Currentdb
Set rs = db.OpenRecordSet("Quotes")

for i = 0 to rs.recordcount
ticker = rs.fields(qTicker")
currDate = rs.Fields("qDate")

query = SELECT TOP 6 qClose, qDate FROM Quotes WHERE qTicker = rs.Fields("qClose") and qDate >= rs.Fields("qDate") ORDER BY qDate;

' Loop recordset, grab first/last value calculate return assign to variable

'assign return variable to new field..

next i

end sub``````

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

ranman256

Well-known Member
you dont do this in code.
You do it using a query

ilcaa

Well-known Member
not sure what you mean...
If i "create" a query, then it will be stand alone query and not part of the original table. Or are you saying create a "Make Table" query this new field included? But then I would have 2 tables with 2 millions rows each ( i could delete the original table). Thing is, I will do this every week... is this the best option? Thanks for your input

Micron

Well-known Member
In case ranman doesn't reply, he means it is bad practice to store a calculated value in a table. You create a calculated field in a query and use that query in whatever manner of display is required. This could be as simple as a datasheet, or it could be a report or form. If one of the values used in the calculation ever changes, the stored result is incorrect.

However, if by "and the next 5 qClose " you mean the first calculation is to be based on qClose in rows 1, 2, 3, 4, 5 and 6, then I do not know how to do that in a calculated query field. There are aggregate functions you can use in a calculation such as Min, Max, First, Last, etc. but if there is one that groups by x rows beyond the current row, it escapes me. If that is what you are doing, then the complexity is up a few notches. You would have to weigh the difficulty of not storing the value against the impact that an incorrect calculation would cause. Perhaps you could live with the risk and mitigate it by deleting the table records after closing a report or form. Otherwise, you might have to loop through a record set using inner and outer loops where you perform the calculation as you've indicated, whereupon you'd base the form's record source on that recordset object.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,199
Messages
5,835,936
Members
430,396
Latest member
dzifna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back