Help needed in Excel

[ebutu555]

Dear Friends,

I'm pulling data from web (NSE India) to excel sheet and refreshing the connection at 5 minutes. Now, every 5 minutes, the data is refreshed. But, I'd like to see the trend of Nifty. So, after the connection is refreshed, I need to copy the data to another sheet along with the time stamp. Something, like below, with column A having the time stamps and column B having the data

A1 B1
10.45 AM 8500
10.50 AM 8555
10.55 AM 8556
11.00 AM 8570

Any help would be highly appreciated. Thanks guys.

 

[DSM]

It's very simple.

1. Assuming If Sheet A has your data.
2. And if your data in Sheet A, Cell A1
3. This data is to be copied in Sheet B, in CellA1
4. Go to Sheet B, Place cursor in cell A1 (which is blank)
5. Press = sign, (The cursor will be live) Go to Sheet A1, Cell A1 and press enter
6. Automatically, data will be replicated in Sheet B, Cell A1 (as a link). So whenever there is a change in data, the same will be updated in Sheet B as well.
7. Having done this in Sheet B, Just copy the formula in B, Cell A!, across the Sheet.
8. Now your full Sheet B will pick up live data and changes in Sheet A.

Time taken = 1 minute.

Dear Friends,

I'm pulling data from web (NSE India) to excel sheet and refreshing the connection at 5 minutes. Now, every 5 minutes, the data is refreshed. But, I'd like to see the trend of Nifty. So, after the connection is refreshed, I need to copy the data to another sheet along with the time stamp. Something, like below, with column A having the time stamps and column B having the data

A1 B1
10.45 AM 8500
10.50 AM 8555
10.55 AM 8556
11.00 AM 8570

Any help would be highly appreciated. Thanks guys.

 

[ebutu555]

Thanks DSM for the quick response. But, I guess I didn't explain my problem clearly. For instance, let the data in sheet 1 is in A20, after the connection is refreshed, I want this data to be copied to sheet 2 in A1 and adding time stamp to B1. After the sheet 1 is refreshed again, the new data should be copied to sheet 2 in A2 and time stamp in B2, so on until end of market time. And, sheet 1 doesn't have timestamp, it has to bee added in sheet 2 when data is copied. Sheet 1 will be live data and sheet 2 will be historic data. I'll make use of sheet 2 data to find the trend. Sorry, if I'm not clear. Thanks again for the response.

 

[gambler]

You need to make a macro for that..
Here it is. http://www.mrexcel.com/forum/excel-...lue-paste-into-next-available-empty-cell.html

Dear Friends,

I'm pulling data from web (NSE India) to excel sheet and refreshing the connection at 5 minutes. Now, every 5 minutes, the data is refreshed. But, I'd like to see the trend of Nifty. So, after the connection is refreshed, I need to copy the data to another sheet along with the time stamp. Something, like below, with column A having the time stamps and column B having the data

A1 B1
10.45 AM 8500
10.50 AM 8555
10.55 AM 8556
11.00 AM 8570

Any help would be highly appreciated. Thanks guys.

 

[DSM]

Well, considering the trend these days, I guess I need you to thank you for appreciating the time/effort in the response.

Not sure if this will help, and the question is only of time stamp, one easy go around is to populate sheet B, with pre-filled time, assuming your script is fairly liquid, and should be traded at regular intervals... of 9:15, 9:20 etc...

My 2C.

Thanks DSM for the quick response. But, I guess I didn't explain my problem clearly. For instance, let the data in sheet 1 is in A20, after the connection is refreshed, I want this data to be copied to sheet 2 in A1 and adding time stamp to B1. After the sheet 1 is refreshed again, the new data should be copied to sheet 2 in A2 and time stamp in B2, so on until end of market time. And, sheet 1 doesn't have timestamp, it has to bee added in sheet 2 when data is copied. Sheet 1 will be live data and sheet 2 will be historic data. I'll make use of sheet 2 data to find the trend. Sorry, if I'm not clear. Thanks again for the response.

 

[ebutu555]

Well, considering the trend these days, I guess I need you to thank you for appreciating the time/effort in the response.

Not sure if this will help, and the question is only of time stamp, one easy go around is to populate sheet B, with pre-filled time, assuming your script is fairly liquid, and should be traded at regular intervals... of 9:15, 9:20 etc...

My 2C.

Refreshing @ 5 minutes is not constant. I'll change the connection refresh to different intervals @ 5m, 15m, 30m or 60m based on my requirement. Hence, I need the time stamp when sheet1 is refreshed or when data is copied to sheet2.

 

[ebutu555]

You need to make a macro for that..
Here it is. http://www.mrexcel.com/forum/excel-...lue-paste-into-next-available-empty-cell.html

Thanks Gambler. I took a cue from the link, amended it and managed to come up with the macro I needed. It might not be perfect but solves my problem. Hope it helps others too

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Address = "$F$45" Then (which ever cell you want to copy)
Sheets("Sheet1").Range("F45").Copy
If Sheets("Sheet2").Cells(1, 1) = "" Then
Sheets("Sheet2").Cells(1, 1).PasteSpecial
Else
i = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column + 1
j = Sheets("Sheet2").Cells(2, Columns.Count).End(xlToLeft).Column + 1
Sheets("Sheet2").Cells(1, i).PasteSpecial (Displays historic data in A1, B1, C1, etc)
Sheets("Sheet2").Cells(2, j) = Now() (Displays time in A2, B2, C2, etc)
End If
End If
End Sub

 

[sherwinstocks]

hi

Can you share the excel sheet, i am also looking for the same

Cheers
:thumb: