Help :( Import From WebPage, put the value in a Table/ repeat that the next day with new value/ New Line in table.. and Graph

[stevekho]

Hey Guys,

I really need your help.

I need to import from a website a value each day (ok) and do a table and graph with them.

My problem is I dont know how to make a graph with these numbers. In other words, how to jump a line in the table each day and copy this value in the table

Thanks a lot!

 

[Ruddles]

Let's start at the beginning. Can you let us have the URL of the Web page so that we can see what we're working with?

 

[stevekho]

Thanks a lot,

So basically it is coming from
http://www.transcanada.com/stockinformation.html
I will copy that table each day around 4 pm and refresh the last value ( first line first value)

 

[Ruddles]

See how you get on with this. Create a new general code module in a copy of your workbook (or create a new workbook for this purpose) and paste this code into it. Create a new worksheet and change the bit in red so it points to the new worksheet. Run the code.

It does a Web query and temporarily puts the data just under your daily data, then moves this data into a new day's row and deletes the temporary data. All the cell formatting is done in the code so there shouldn't be any need to do any manually.

There's no graphing yet - let's get the download of the data working first.

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Const sURL As String = "[/FONT][URL="http://investor.shareholder.com/ctrp/new/stockquote.cfm"][FONT=Fixedsys]http://investor.shareholder.com/ctrp/new/stockquote.cfm[/FONT][/URL][FONT=Fixedsys]"
Const sSheet As String = "[COLOR=red][B]Sheet1[/B][/COLOR]"[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Dim ws As Worksheet
Dim iLastRow As Long
Dim iColumn As Integer[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub GetStockInfo()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim oCell As Range
  
  Set ws = ThisWorkbook.Sheets(sSheet)
  
  iLastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
  
  Application.ScreenUpdating = False
  
  If iLastRow = 1 Then
    ws.Range("A1:P1").Font.Bold = True
    ws.Range("A1:P1").Value = Array( _
    "Date", "Last", "Change", "% Change", "Open", "Bid", "Ask", "High", "Low", "Prev. Close", _
    "Year High", "Year Low", "Volume", "EPS", "Mkt Cap", "Last Q'ly Div/Share")
        Columns("A:P").Select
    ws.Range("A1:P1").HorizontalAlignment = xlCenter
  End If[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Call GetWebPage
  
  For Each oCell In Range("A" & CStr(iLastRow + 4) & ":C" & CStr(iLastRow + 13))
    oCell = Replace(oCell.Text, "$", "")
    oCell = Replace(oCell.Text, "*", "")
    oCell = Replace(oCell.Text, "/ Share", "")
  Next oCell
    
  ws.Cells(iLastRow + 1, 1) = Format(Now(), "dd/mm/yyyy")
  
  Call MoveData
  
  Call FormatRange
  
  ws.Columns("A:P").EntireColumn.AutoFit
  For iColumn = 1 To 16
    ws.Columns(iColumn).ColumnWidth = ws.Columns(iColumn).ColumnWidth * 1.1
  Next iColumn
    
  Application.ScreenUpdating = False
  
End Sub[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub FormatRange()
  
  ws.Range("A" & CStr(iLastRow + 1)).NumberFormat = "dd/mm/yyyy;@"
  ws.Range("B" & CStr(iLastRow + 1) & ":C" & CStr(iLastRow + 1) & "," _
     & "E" & CStr(iLastRow + 1) & ":L" & CStr(iLastRow + 1) & "," _
     & "N" & CStr(iLastRow + 1) & "," _
     & "P" & CStr(iLastRow + 1)).NumberFormat = "[$$-1009]#,##0.00"
  ws.Range("D" & CStr(iLastRow + 1)).NumberFormat = "0.00%"
  ws.Range("M" & CStr(iLastRow + 1) & ",O" & CStr(iLastRow + 1)).NumberFormat = "#,##0"
  ws.Range("A" & CStr(iLastRow + 1) & ":P" & CStr(iLastRow + 1)).HorizontalAlignment = xlCenter[/FONT]
[FONT=Fixedsys]End Sub[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub GetWebPage()
      
  With ActiveSheet.QueryTables.Add(Connection:="URL;" & sURL, Destination:=Sheets(sSheet).Range("A" & CStr(iLastRow + 2)))
    .Name = "stockquote_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "3"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
  End With
  
End Sub[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub MoveData()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]    Range("A" & CStr(iLastRow + 5) & ":C" & CStr(iLastRow + 5)).Copy Destination:=Range("B" & CStr(iLastRow + 1) & ":D" & CStr(iLastRow + 1))
    Range("A" & CStr(iLastRow + 7) & ":C" & CStr(iLastRow + 7)).Copy Destination:=Range("E" & CStr(iLastRow + 1) & ":G" & CStr(iLastRow + 1))
    Range("A" & CStr(iLastRow + 9) & ":C" & CStr(iLastRow + 9)).Copy Destination:=Range("H" & CStr(iLastRow + 1) & ":J" & CStr(iLastRow + 1))
    Range("A" & CStr(iLastRow + 11) & ":C" & CStr(iLastRow + 11)).Copy Destination:=Range("K" & CStr(iLastRow + 1) & ":M" & CStr(iLastRow + 1))
    Range("A" & CStr(iLastRow + 13) & ":C" & CStr(iLastRow + 13)).Copy Destination:=Range("N" & CStr(iLastRow + 1) & ":P" & CStr(iLastRow + 1))
    Range("A" & CStr(iLastRow + 2) & ":C" & CStr(iLastRow + 13)).ClearContents
    
End Sub[/FONT]

 

[stevekho]

It is amazing! Thanks a lot!!!!
It is more than perfect, I did the graph and everything is working well. Thanks for everything!!

 

[stevekho]

I just have a last question, if you could help,

basically I have a table with different values.

I want to create an other small table below which basically summarizes some of the data (average etc...)
Nonetheless, I would like the table to refresh by itself everytime I am adding a line.
When I add a line or a date in that case (like May 7), I just would like the formula inside the table to also shift by 1. As a result It will estimate the new report for the new date.

I know it should be easy but I am having trouble :/

Ex

Cell has B32. When I add a line and shift the table by one line, I would like that cell to become B33. ( As a result it will estimate everything for the new date)

Thanks a lot

 

[Ruddles]

...everything is working well.

Good grief - it's great when something works first time! Do keep an eye on it though - check everything gets transferred to the correct cells, etc - and let me know if there are any glitches.

 

[Ruddles]

I just have a last question, if you could help

You might be better starting a new thread if this is essentially a different topic. That way more people will see it.

I have a table with different values.

I want to create an other small table below which basically summarizes some of the data... When I add a line or a date in that case (like May 7), I just would like the formula inside the table to also shift by 1.

How do you add a line in the larger table? By inserting a complete new row? So the smaller table gets shifted down by one row each time?

 

[stevekho]

"How do you add a line in the larger table? By inserting a complete new row? So the smaller table gets shifted down by one row each time?"

Yep I just add a new row each time with the new date. Hence, the smaller table gets shifted down by one. I would like its formulas to also be shifted by one. As a result it will estimate everything for the new line (avg etc..). Perhaps there is another way but I think that one might be easier and wont involve macro.

Thanks a lot!

 

[Ruddles]

There are probably several ways of doing this but the INDIRECT function springs to mind. Follow this trivial example:-

Create a 'larger table' of numbers - any numbers - in cells A1 to C6. Let's say you want the average of the numbers in the last row of the 'larger table' - cells A6, B6 and C6 - to appear in your 'smaller table' in cell A9. Put this in A9:-

=AVERAGE(INDIRECT("A"&ROW()-3),INDIRECT("B"&ROW()-3),INDIRECT("C"&ROW()-3))

The INDIRECT always points 'up three rows' wherever it's located.

Now insert a new row below row 6 so it becomes the new row 7. The formula which was in A9 pointing up three rows to the last row of the larger table on row 6, is now in A10 pointing up three rows to the new last row of the larger table on row 7. Put some numbers in A7, B7 and C7.

The formula in A10 should now give you the average of A7, B7 and C7.

Insert a new row 8 and put some numbers in A8, B8 and C8.

Yes?