Excel - adding Stocks to multiple worksheets

Discussion in 'Trading Software' started by sargie, Nov 15, 2005.

  1. sargie


    Hi Guys. I have a s/sheet with multiple worksheets, with the 'Summary' containing a list of all stocks (vertically in Columns) grouped by industry . I have other worksheets tracking data about the stock (horizontally in Rows). However, I'd like to dynamically be able to Add a new stock to the Summary sheet and it to automatically add to the other worksheets.

    I've thought about using Ranges etc but having no luck. Also, I may try my luck at FORMS but this would require considerable effort.

    Thanks in advance. Here's a brief example ;
  2. GoldenKid


    Not sure if this is what you were looking for. Hope it helps.
  3. sargie


    Thanks for your reply. Unfortunately that doesnt work - well, it does, but its purely referencing the 'Summary' sheet.

    Ideally, I'd like some form of Vb code so that if I inserted a new stock on 'Summary' it automatically updates in the other two worksheets? And its not restricted to the vertical/horizontal basis - I will change so that all s/sheets have the stocks listed vertically in Columns.

    Thanks again.
  4. Here's some "range" code that should help...

    Sub RowRange()

    Dim r As Range
    Dim c As Range

    Set r = ThisWorkbook.Sheets("November").Rows(3)

    For Each c In r.Cells
    If WorksheetFunction.IsText(c.Value) Then
    MsgBox "Location " & c.Address(False, False) & " contains... " & c.Value
    End If
    Next c

    End Sub

    The caveat is adding or deleting range items will negate the range variable. In these cases, the range variable must be re-inited to avoid undefined behavior. However, this does not apply if you merely change the contents of range items.

    Good luck,
  5. Probably a stupid question, but why not try a database?
    A relational database can do this easily.
  6. Can a relational database be updated through DDE?

  7. Yes, DDE works also in Access according to Microsoft. But i never tried it out.
  8. sargie


    Yes - thought about that. However, its a very informal s/sheet and if anything, the numbers running off the back of the tickers are the main priority - ie, stats/charts etc.

  9. Doesnt sound all that hard to do.

    After 2 minutes of brainstorming, i suggest this.

    Either programatically, or by manually executing a VBA routine, scan the entire summary sheet, look in your other pages as each symbol is scanned for the existance of that symbol.

    If not found, then add it.

    I'll code it for you for $500.