How to add new row each day automatically?

[viktor_david]

Hi,

I have the following problem:
I want a way that tomorrow there is a new Row (thanks for correction,Joe4) automatically in my excel that shows the date and the price.
So today it shows like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]A[/TD]
[TD="align: center"]02.05.2017[/TD]
[TD]320[/TD]
[/TR]
[TR]
[TD="align: right"]B[/TD]
[TD="align: center"]01.05.2017[/TD]
[TD]310[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And tomorrow I want it to look like this without me having to insert a new row manually:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]A[/TD]
[TD="align: center"]03.05.2017[/TD]
[TD]315[/TD]
[/TR]
[TR]
[TD="align: right"]B[/TD]
[TD="align: center"]02.05.2017[/TD]
[TD]320[/TD]
[/TR]
[TR]
[TD="align: right"]C[/TD]
[TD="align: center"]01.05.2017[/TD]
[TD]310[/TD]
[/TR]
</tbody>[/TABLE]

How can I do this?
Note: I know nothing about VBA.

Is it faster for excel to open when it's filled with macros or when it's filled with formulas?

Thanks guys

 

[Joe4]

Looks like you want a new row, not a new column.

Where exactly is the price value coming from?

What happens if this file is not opened at all on a day?
Should it skipped the day it missed, or backfill all the missed days?

 

[viktor_david]

Thanks for the correction, Joe.
The price value is coming from Infront. Somethign like bloomberg.
If the file is not opened then I guess no update will be done, however, when it is opened on the following day, I would expect to have updates for the previous day in addition to the day's update.

Unless it is too much trouble, then we will open it every day so it updates itself.

 

[Joe4]

It is easy enough to create automated VBA code that whenever the file is opened, will insert rows and populate all dates since the last date to the current date, but I am not sure I understand how you think it is going to automatically get the price.

 

[viktor_david]

And.. Is it possible as well to copy the formula from bellow to the newly inserted row?
Because if that is the case, given the date, the formula can refer to the date cell. Do you get it?
say A1 is the date and B1 is ="price of X given A1 date"
Then when one row is insterted, it adds a new A1 with the newest date, and new B1 with the very same formula but refering to the new date

 

[Joe4]

OK, try this VBA code.

Go into the VBA Editor, and make the VBA Project Explorer visible, if it is not already.
Then find your file name, and expand the Microsoft Excel Objects tree under that, if not already done so.
Double-click on the "ThisWorkbook" module, and paste this code in the resulting VB Editor window:

Private Sub Workbook_Open()

    Dim lastDate As Date
    
'   Go to the sheet with prices
    Sheets("Sheet1").Activate
    
'   Check the first value
    lastDate = Range("A1")
    If lastDate < Date Then
        Do Until lastDate = Date
            Rows(1).Insert
            lastDate = lastDate + 1
            Range("A1") = lastDate
            Range("B2").Copy Range("B1")
        Loop
    End If
    
End Sub

A few things you need to make sure of in order for it to work:
- Column A is formatted as valid dates (and NOT text)
- If the sheet this is on is named anything other than "Sheet1", make the appropriate change in the VBA code above
- Make sure Macros are Enabled

Now, whenever you open this file, it will automatically look at the date listed in cell A1, and insert rows and dates until it gets to the current date.

 

[viktor_david]

It can be done this way (where B1 shows the formula and B2-B4 show what excel actually shows)
[TABLE="width: 500"]
<tbody>[TR]
[TD]01.05.2017[/TD]
[TD]=RTD("ontrade.hist";"";"TRS";"AYA";"PricesByDate";A1;"open";"CW")[/TD]
[/TR]
[TR]
[TD]30.04.2017[/TD]
[TD]23,96
[/TD]
[/TR]
[TR]
[TD]29.04.2017[/TD]
[TD]23,5[/TD]
[/TR]
[TR]
[TD]28.04.2017[/TD]
[TD]23,72[/TD]
[/TR]
</tbody>[/TABLE]

Or this way: (where B1 and C2 show the formula and B2-C4 show what excel actually shows)
Note here that A column has to be inputed like numbers in order to show it.. so the formula assumes "0" is today and "1" is yesterday and so on
[TABLE="width: 500"]
<tbody>[TR]
[TD]0
[/TD]
[TD]=RTD("ontrade.hist";"";"TRS";"AYA";"Prices";A1;"date")[/TD]
[TD]=RTD("ontrade.hist";"";"TRS";"AYA";"Prices";A1;"open")[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30.04.2017[/TD]
[TD]23,96
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]29.04.2017[/TD]
[TD]23,5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]28.04.2017[/TD]
[TD]23,72[/TD]
[/TR]
</tbody>[/TABLE]

How is the best way to do it?

 

[viktor_david]

You are the boss, boss...
It did it...

I get one problem now that, the trading days when market is closed, like weekends, it shows the date on A column but a blank cell in B column for that day, that is not ideal... any idea on how to fix this?

 

[Joe4]

What do you want to happen in that case?
We could add code in there to not insert rows for weekend dates, if you like.

That variation of the code would look like this:

Private Sub Workbook_Open()

    Dim lastDate As Date
    
'   Go to the sheet with prices
    Sheets("Sheet1").Activate
    
'   Check the first value
    lastDate = Range("A1")
    If lastDate < Date Then
        Do Until lastDate = Date
            lastDate = lastDate + 1
            If Weekday(lastDate, vbMonday) < 6 Then
                Rows(1).Insert
                Range("A1") = lastDate
                Range("B2").Copy Range("B1")
            End If
        Loop
    End If
    
End Sub

 

[viktor_david]

Is it possible that instead of dates it inputs numbers? so basically A1 today is 0, yesterday's date is 1, before yesterday is 2. And tomorrow, tomorrow's date will be 0, today's date will be 1, yesterday's date 2, and before yesterday's date will be 3.
Is it possible?