need to automate calender update on new line each day

July 31, 2014 at 12:26:59
Specs: Windows 64
I have a spreadsheet that pulls the latest stock price data for several stocks each day. this info is updated all day long as the price fluctuates. At the end of the day (3:00 pm central) I want the date in the left hand column to update to the next day on the next line and freezing the last price on the original date ,line

See More: need to automate calender update on new line each day

Report •

July 31, 2014 at 12:31:12
How are your updates happening? Do you have some kind of automatic process? Please give us some details.

Bottom line is that you are probably going to need a macro that gets triggered at 3 PM Central to lock in your values and add the date. before a macro could be written, we would need to know more about the layout and operation of your sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 31, 2014 at 12:50:56
column a are the dates, next column the last price, To get the price updates the formula for that cell is =RTD("esrtd",,"IBM", "LAST"). (this would be for IBM price updates.) At this point I don't believe it pulls any time info, because the next day that line will continue to update if I don't move it to the current days line.

Report •

July 31, 2014 at 13:16:55
Your OP says that you pull prices for "several stocks", your response says "next column the last price".

Are you using one row for multiple stocks..something like this:

   A             B             C            D 
  Date          IBM           MSFT         BIDU
7/29/2014      194.57        43.89        220.00
7/30/2014      194.00        43.58        219.13
7/31/2014      191.63        43.25        215.96

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

July 31, 2014 at 13:25:35

Report •

August 1, 2014 at 09:56:31
OK, give this a try...

The way this should work is as follows:

The Workbook_Open() code will schedule the LockPrices() code to run at 3:00 PM assuming the workbook is Open at 3:00PM and the focus of Excel is on that workbook.

Note: As far as I can tell from my testing, the system's focus does not have to be on Excel, but Excel's focus must be on the workbook with the macros. In other words, you can't be working in another workbook at 3:00PM because that will take Excel's focus away from the workbook that contains the macro you want to run at 3:00PM. There may be a way around that issue, but I don't know how to make it happen and don't have time to investigate it. If you want to Google around for a solution and point me towards it, I'll see what I can do.

So, back to the code...

The LockPrices() code will Copy the current last Row of Sheet1 to the Row below it. That will copy the formulas so that they can update the next day. After the formulas are copied to the Row below, the code will then lock the values in the Row above by using PasteSpecial...Values.

In my testing, I use =TODAY() in Column A to display the current date. That way when the formulas get copied, the TODAY() function goes into the new last Row along with the RTD formulas. For the rest of that day, i.e. after 3:00, the new last row will still reflect data for the current day but once the clock hits midnight the TODAY() function will update the next time the sheet calculates. As soon as the market opens, the RTD functions should begin to show the last market value for the securities. I did not test the RTD functions themselves, I simply used other formulas to test the concept.

In order to use these macros, they have to be placed in the proper modules. The Workbook_Open macro must be placed in the ThisWorkbook module. This is the code that will tell Excel to schedule the LockPrices macro to run at 3:00PM. The workbook must be Saved, Closed and then Opened in order for the scheduling to occur.

If you want to test the process, you can simply change the time within the quotes, as long as you Save, Close, and Open the workbook each time.

Private Sub Workbook_Open()
'Schedule LockPrice code macro to run at 3:00PM
    Application.OnTime TimeValue("15:00:00"), "LockPrices"
End Sub

The following is the code that will actually do the copying, pasting, etc. It needs to be placed in a standard module within the VBA editor (Insert...Module)

Sub LockPrices()
'Turn off ScreenUpdate
  Application.ScreenUpdating = False
'Determine Last Row with data
   lastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Copy last Row, with formulas, to next Row
     Sheets(1).Cells(lastRow, 1).EntireRow.Copy _
        Sheets(1).Cells(lastRow + 1, 1)
'Lock values for current day
     Sheets(1).Cells(lastRow, 1).EntireRow.Copy
     Sheets(1).Cells(lastRow, 1).PasteSpecial Paste:=xlValues
'Turn off CutCopyMode to eliminate Copy lines
     Application.CutCopyMode = False
'Turn on ScreenUpdate
     Application.ScreenUpdating = True
End Sub

Let me know how these work for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

Ask Question