Report

Transfering data from one sheet to another

Ask a question sauravghai20 6Posts Friday November 25, 2016Registration date November 25, 2016 Last seen - Last answered on 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.
See more 
Helpful
+0
moins plus
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?
yg_be 842Posts lundi 9 juin 2008Registration date December 8, 2016 Last seen - Nov 25, 2016 at 12:36 PM
Please publish your file and post the location here, so that the forum may help you.
Reply
sauravghai20 6Posts Friday November 25, 2016Registration date November 25, 2016 Last seen - Nov 25, 2016 at 01:50 PM
Ok.

Please find below link.

https://www.dropbox.com/s/xohgf6e6aj3r29z/Trading%20Data.xlsm?dl=0

In this file Getdata sheet is master sheet. In which I can get data for any script written against symbol cell by selecting interval time of 30 minutes. of 20 days data.

I have put data of some scripts in their respective sheets for reference.

Now the thing is that master sheet can only update the 60 days data, but I have taken 20 days data for reference. When on next day I update data, the data of first day would be erased and would be replaced by new data of current day. Now I have made separate sheet of each script. If I would do all the things manually it would take enough time. I want that whenever I update data on mastersheet by selecting particular script. the script sheet should automatically updated with new data, but without erasing data in script sheet.

I hope you will now understand.

Thanks
Reply
yg_be 842Posts lundi 9 juin 2008Registration date December 8, 2016 Last seen - Nov 25, 2016 at 03:09 PM
You want to append only the new content of the GetData sheet into the sheet having the name identical to the Symbol? You want to keep previous data in the symbol sheet, and only add the new data corresponding to that symbol, is that right?
In the file you shared, it is not obvious to see what has been done and what still need to be done.
If I do not understand, I suggest you explain it based on the file you shared, perhaps showing a before and after file.
Reply
sauravghai20 6Posts Friday November 25, 2016Registration date November 25, 2016 Last seen - Nov 25, 2016 at 11:48 PM
You want to append only the new content of the GetData sheet into the sheet having the name identical to the Symbol? You want to keep previous data in the symbol sheet, and only add the new data corresponding to that symbol, is that right?

Yes Sir. Absolutely right, I want to append only new data in the corresponding Excel sheet. GetData sheet is the master sheet from where I can get all the data of the symbols. For me it is easy to select every script and update using the master sheet. But at the same time the corresponding symbol sheet should also be updated with addition of new data.

In this file in GetData sheet, I select the symbol, want high low and close data of 30 minutes. and the maximum data I could extract from is upto last 60 days. So, every day when I update data, there will be addition of new data and the first date data would be erased. So, I want that I should make a separate sheet for every symbol. So, I could maintain data for long way back. This could be done manually i.e. selecting the particular symbol -> copying new data -> and paste it in the corresponding sheet.but it will consume enough time.

So, I am looking for something could be done that. Everyday I would update data on master sheet with every symbol and the new data which would be populated should automatically append in the corresponding symbol sheet without erasing previous data in the symbol sheet.

Hope if I am able to explain it rightly.

Thanks for considering my queries.
Reply
yg_be 842Posts lundi 9 juin 2008Registration date December 8, 2016 Last seen - Nov 25, 2016 at 04:32 PM
I believe you VBA code copies the data of interest from the Data Sheet to the GetData sheet.
If that is correct, you simply need to adapt the code to change the destination of the copy : the sheet corresponding to the Symbol, and the line corresponding to the right moment.
Did you write or do you understand the code you are using for your business?
Reply
Add comment
Helpful
+0
moins plus
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
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!