External Data

[oyarar]

Hello,

I need help about external data on Excel 2007.

I would like to create a database of daily closing rates of EUR/$. I can
get the data from central banks, msn money central or finance yahoo.
However, the external data connection in Excel is replacing the old data
with an updated one. Say, if the data is on A2, only the A2 is changing as
new data comes up. Instead I want it to put the new data to a row below
(A3, and A4 so forth...), so that I can keep track of histrocial dataset.
Could anyone please tell me how can I do that?

Thanks a lot.

[Ago]

insert new row before update?

[rbrhodes]

need to see the code thta is pulling the data

[oyarar]

@rbrhodes that is through excel ->Data->From Web or Data->Existing Connections. I did not write any code for it.

@Ago
if I insert row, the data link just shifts from A1 row to A2 row. When I refresh the connection only A2 value changes and .A1 remains blank. Instead I want to keep A1 value and when I refresh the data, the new one should be inserted to row below and so on.

[rbrhodes]

Hi oy,

In Excel 2007: Data>Existing Connections. When you've selected the connections you get a dialog box 'Import Data' asking where to put the data. At the bottom left of that dialog box click 'Properties'. Click that and at the bottom of that you will see three choices:

1) Insert cells
2) Insert entire rows
3) Overwrite existing

It's the same in older versions as well, just different toolbar names

Is that it?

[oyarar]

hi rbrhodes, thank you for your response.

I tried all three options however, insert cells and insert entire rows just expands if datasource has new variable.

say you are importing stock information as
close :A1
high :A2
low :A3

and one day the source file expands as
close A1
volume A2 (new variable)
high A3
low A4


so the insert cells and insert entire rows helps you to shift high and low value one row down. otherwise if you select overwrite existing, it will continue to paste new "volume" variable on previous "high" variable.

Instead I just want,
A1: 01 Jan 2010 Eur/$ value

the day later when I refresh it

A1: 02 Jan 2010 Eur/$ value
A2: 01 Jan 2010 Eur/$ value

so on...

[Crocus Crow]

I think you will need some VBA code to do what you want, and it's easier with the web query and historical data on different sheets. With the web query on, say, Sheet3 or Sheet2, put this code in its sheet module and it will copy the web data to the next row on Sheet1 (the sheet to be updated with historical data) every time the query refreshes.
[vba]Private Sub Worksheet_Change(ByVal Target As Range)

Dim qt As QueryTable
Dim row As Long

If QueryTables.Count = 0 Then Exit Sub

Set qt = QueryTables(1)
If Not Intersect(qt.Destination, Target) Is Nothing Then
With Sheet1
row = .Cells(.Rows.Count, "A").End(xlUp).row + 1
qt.ResultRange.Copy
.Cells(row, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With
End If

End Sub
[/vba]

[rbrhodes]

Yeah I was thinking run a macro then update...

[oyarar]

Thank you Crocus Crow, I appreciate your help. The code works smoothly.

One last question, Excel does not let the workbook to update automatically when the file is closed, isn't it? I guess I have to leave the file open.

Thanks again.

[oyarar]

Yeah I was thinking run a macro then update...

Thanks for your interest, rbrhodes!