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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 1, 2016 at 11:22 AM
Related:
- Transfering data from one sheet to another
- Transfer data from one excel worksheet to another automatically - Guide
- Google sheet right to left - Guide
- Free fire id transfer facebook to google - Guide
- Windows network commands cheat sheet - Guide
- Tmobile data check - Guide
2 responses
yg_be
Posts
23383
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
December 9, 2024
5
Nov 25, 2016 at 03:16 AM
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?
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?
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Dec 1, 2016 at 11:22 AM
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):
Best regards,
Trowa
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
Nov 25, 2016 at 06:56 AM
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.
Nov 25, 2016 at 06:57 AM
Nov 25, 2016 at 12:19 PM
Nov 25, 2016 at 12:21 PM
Nov 25, 2016 at 12:36 PM