Previous Topic Next Topic

Transfering data from one sheet to another

Closed
sauravghai20 Posts 6 Registration date Friday November 25, 2016 Status Member Last seen November 25, 2016 - Nov 25, 2016 at 02:26 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 1, 2016 at 11:22 AM
Hello!
I am running my own business.
There are many ways my below query help me in my business.

I buy raw material for my business like Nickel, Zinc, Steel, Etc. So, I keep track of commodities online.

I have got some excel sheet regarding the stock prices which I could replace it with commodities.

In this sheet I get the data on different time frames. Like if I am using 30 minutes data. I could get maximum 60 days of data at the interval of 30 minutes. And there is an option for getting the data. I could change the name of commodities and could get the data particularly.

My query is that though I could get record of 60 days data, which means every new day data will be updated but the data of first day would be erased.

If there is any possibility, if I could maintain different sheets for different commodities once manually for each with the data starting from today. But from very next day I want that new data should be kept on adding in their respective sheets without adding previous data, when I refresh the data with a particular commodity name on main sheet.

Hope you will understand and look for valuable comments.

Thanks in advance.

2 responses

Answer 1 / 2
yg_be Posts 22764 Registration date Sunday June 8, 2008 Status Contributor Last seen May 7, 2024 5
Nov 25, 2016 at 03:16 AM
Do you mean you have a single sheet where you obtain the data for a chosen commodity, and you want that data to be appended to another sheet?
Can you clarify what you mean by "without adding previous data" ?
Do you perhaps mean that, when you obtain the data, you obtain too much data, and you only want to transfer new data to the commodity sheet?
0
sauravghai20 Posts 6 Registration date Friday November 25, 2016 Status Member Last seen November 25, 2016
Nov 25, 2016 at 06:56 AM
Hi YG. Yes to your Question.

Without adding data was a typo error.

Please find attached one is master sheet, from where I can obtain data of every 30 minutes interval of particular scrip by entering scrip name and selecting the interval and then get data. I can get as many as scripts listed on NSE. I want to maintain separate sheet for each script. As you can see in the Master sheet there is data of two days i.e. of 25.11.2016 and 24.11.2016. Suppose I have maintained separate sheets of all scripts like I did for PNB and AXIS with data as of today for two days.

There is one limitation that I could get data of maximum 60 days with this sheet for every script. I want that now in master sheet whenever I change the script name and click get data. The new data should automatically get copied to their respective sheets i.e. there should be addition of this new data.

Like in this example on 26.11.2016 when I update data on master sheet for every script. The new data of particular script i.e. of 26.11.2016 should be populated in their respective sheets.

I hope if am able to explain it properly.
0
sauravghai20 Posts 6 Registration date Friday November 25, 2016 Status Member Last seen November 25, 2016 > sauravghai20 Posts 6 Registration date Friday November 25, 2016 Status Member Last seen November 25, 2016
Nov 25, 2016 at 06:57 AM
Sorry, Picture is not getting attached
0
yg_be Posts 22764 Registration date Sunday June 8, 2008 Status Contributor Last seen May 7, 2024 5
Nov 25, 2016 at 12:19 PM
Can you share your Excel file?
0
sauravghai20 Posts 6 Registration date Friday November 25, 2016 Status Member Last seen November 25, 2016 > yg_be Posts 22764 Registration date Sunday June 8, 2008 Status Contributor Last seen May 7, 2024
Nov 25, 2016 at 12:21 PM
Can you please provide me you mail id
0
yg_be Posts 22764 Registration date Sunday June 8, 2008 Status Contributor Last seen May 7, 2024 5
Nov 25, 2016 at 12:36 PM
Please publish your file and post the location here, so that the forum may help you.
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 1, 2016 at 11:22 AM
Hi Saurav,

This is your code with a few extra lines (11, 20, 117, 118 are the added lines): 
Option Explicit

Sub LiveData()
Dim ParameterSheet As Worksheet
Dim DataSheet As Worksheet
Dim ticker As String
Dim exchange As String
Dim interval As Integer
Dim numPastTradingDays As Integer
Dim qurl As String
Dim sRow As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set ParameterSheet = Sheets("GetData")
Set DataSheet = Sheets("Data")

sRow = ParameterSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
DataSheet.Cells.Clear
Range("A2:F10000").Select
Selection.ClearContents
ticker = ParameterSheet.Range("symbol").Value
exchange = ParameterSheet.Range("exchange").Value
interval = ParameterSheet.Range("interval").Value * 60
numPastTradingDays = ParameterSheet.Range("periods").Value

qurl = "http://www.google.com/finance/getprices?" & _
"q=" & ticker & _
"&x=" & exchange & _
"&i=" & interval & _
"&p=" & numPastTradingDays & "d" & _
"&f=d,o,h,l,c,v"

QueryQuote:
With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

DataSheet.Range("a1").CurrentRegion.TextToColumns Destination:=DataSheet.Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False

DataSheet.Columns("A:G").ColumnWidth = 12

'===Convert Google timestamp to Excel timestamp (only for Windows)
Dim timeStamp As Double
Dim timeStampRaw As String
Dim timeZoneOffsetRaw As String
Dim timeZoneOffset As Variant
Dim numRows As Integer
Dim i As Integer
numRows = DataSheet.UsedRange.Rows.Count - 1

timeZoneOffsetRaw = DataSheet.Range("a7")
timeZoneOffset = (Mid(timeZoneOffsetRaw, InStr(timeZoneOffsetRaw, "=") + 1, 10))

For i = 8 To numRows

If Not IsNumeric(DataSheet.Range("a" & i)) Then

timeStampRaw = DataSheet.Range("a" & i)
timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) - 1))
timeStamp = (timeStamp + timeZoneOffset * 60)
DataSheet.Range("g" & i) = timeStamp / 86400 + 25569

Else

DataSheet.Range("g" & i).FormulaR1C1 = "=(RC[-6]*" & interval & "+" & timeStamp & ")/86400+25569"

End If

Next

DataSheet.Range("g8:g" & numRows).NumberFormat = "d mmm yyyy h:mm;@"
DataSheet.Range("G:G").Columns.AutoFit

'''''''''''''''''''''''''''''''''
Dim lrA As Integer

lrA = DataSheet.Range("B" & Rows.Count).End(xlUp).Row

DataSheet.Range("G8:G" & lrA).Copy
Sheet3.Range("A2").PasteSpecial Paste:=xlPasteValues
Sheet3.Range("A2:A" & lrA).NumberFormat = "d mmm yyyy h:mm;@"
Sheet3.Range("A:A").Columns.AutoFit

DataSheet.Range("E8:E" & lrA).Copy
Sheet3.Range("B2").PasteSpecial Paste:=xlPasteValues

DataSheet.Range("C8:C" & lrA).Copy
Sheet3.Range("C2").PasteSpecial Paste:=xlPasteValues

DataSheet.Range("D8:E" & lrA).Copy
Sheet3.Range("D2").PasteSpecial Paste:=xlPasteValues

DataSheet.Range("B8:B" & lrA).Copy
Sheet3.Range("E2").PasteSpecial Paste:=xlPasteValues

DataSheet.Range("F8:F" & lrA).Copy
Sheet3.Range("F2").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = xlCopy

'''''''''''''''''''''''''''''''''

Application.Calculation = xlCalculationAutomatic

Range(Cells(sRow, "A"), Cells(Range("F" & Rows.Count).End(xlUp))).Copy _
Sheets(Range("I13").Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub


Best regards,
Trowa
0

Similar discussions

AMD data change update new data to DMI
Luis -
dwebb -

1 response
Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
copy a row from one worksheet to another, based on criteria
wy -
vcoolio -

15 responses