VBA Best Practice, Create New Calculated Column

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,954
you dont do this in code.
You do it using a query
add the extra field there.
 

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686
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
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top