chart/query help

[conorn55]

Hi guys
So I have made an excel document containing the price of an asset on two different exchanges, i have a query set up so that it gets the information from the server and automatically update the two cells every minute, that all works fine. However I would like to plot the change in the price over time (as i have no access to historical data for the assets from the server). I am having lots of trouble with this, as when ever the cell updates it overwrites the previous data and I no longer have access to it.
Is there any way to save the data somewhere before it is overwritten ?
Any help would be appreciated.

Thanks in advance

Conor

 

[JustynaMK]

Hi Conor,

It is possible to save the values using the Change Event. Assuming that your data is in Sheet1, go to VBA window (Alt+F11), double-click on "Sheet1" and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lngLastRow          As Long


    If Intersect(Target, Range("[COLOR=#ff0000][B]A1[/B][/COLOR]")) Is Nothing Then Exit Sub
    
    If Len(Range("[B][COLOR=#ff8c00]D1[/COLOR][/B]")) > 0 Then
        lngLastRow = Cells(Rows.Count, "[COLOR=#ffa500][B]D[/B][/COLOR]").End(xlUp).Row
        Range("[COLOR=#ff8c00][B]D[/B][/COLOR]" & lngLastRow + 1).Value = Range("[COLOR=#ff0000][B]A1[/B][/COLOR]").Value
        Range("[COLOR=#40e0d0][B]C[/B][/COLOR]" & lngLastRow + 1).Value = Format(Now(), "yyyy-MM-dd hh:mm:ss")
    Else
        Range("[COLOR=#ff8c00][B]D1[/B][/COLOR]").Value = Range("[COLOR=#ff0000][B]A1[/B][/COLOR]").Value
        Range("[COLOR=#40e0d0][B]C1[/B][/COLOR]").Value = Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If


End Sub

If the value of your stock is stored in cell A1, then every time its value changes it is being automatically saved to column D (value) along with the timestamp in column C. Please give it a try and let me know if it works for you.