LearnN Spreadsheet for Stock Market Trading, Day Trading, Swing Trading and Investing --- Source

SOURCE HERE:

'-------------------------------------------------------------------

'  LearnN.ODS:   SPREADSHEET FOR STOCK & OTHER SECURITY TRADING 
'                DAYTRADING, SWING-TRADING, INVESTING
'                by:       Rob Hock 2024
'                e-mail:   learnN@robhock.net
'                www:      https://robhock.net/learnN
'                Version:  1.0 
'-------------------------------------------------------------------
REM  *****  BASIC  *****
Option VBASupport 1    'turn lights on

'--------------------------------------
' CONSTANTS
'-------------------------------------- these variables can be updated 
on sheet:
Const C_HistoryBeginRow ="T27"
Const C_BlankTemplateRow = "T28"
Const C_BagsFromRow = "T29"
Const C_BagsToRow = "T30"
Const C_BrokersHere ="T31"
Const C_HolidaysHere ="T32"
Const C_URLtwits = "O27"
Const C_URLsec = "O28"
Const C_URLshorts ="O29"
Const C_URLyahoo ="O30"
Const C_URLuser ="O31"
'------------------------------------- hard coded columns can be changed
 here:
Const C_ColumnDate = 1
Const C_ColumnTicker = 2
Const C_ColumnDTLeft = 3
Const C_ColumnBroker = 5
Const C_ColumnDTStatus = 6
Const C_ColumnShares =7
Const C_ColumnBot = 8
Const C_ColumnSold = 9
Const C_ColumnEarned =10
Const C_ColumnComments =12
Const C_ColumnNET =15
Const C_ColumnPwrRef =17
Const C_BrokersColumnName = 1    'COLS FOR BROKER LOOKUP TABLE
Const C_BrokersColumnPNL = 5
Const C_BrokersColumnNotes4Input = 6
Const C_BrokersColumnNotes4DT = 8
'-------------------------------------- static messages can be changed 
here
Const C_StrDAYTRADE= "DAYTRADE"     '***this is a keyword!
Const C_StrOUT = "OUT"
Const C_StrHOLIDAY = "HOLIDAY"
Const C_StrDELETED = "DELETED "
Const C_StrTRADES =" trade(s)!"
Const C_StrEarned ="earned"
Const C_StrDayRem = " daytrades remaining "
Const C_MsgInputBagsTitle = "BAG input/edit"
Const C_MsgWorkingTitle = "Working ..."
Const C_MsgDeleteHistoryTitle = "Delete History IF..."
Const C_MsgInputTick = "Input Stock Ticker:"
Const C_MsgInputTickTitle = "No ticker on that row!"
Const C_MsgPickaBag = "   Select a BAG first... then try again!   "
Const C_MsgPickaBagTitle = "Pick a bag!"
Const C_MsgAboutUS = "******* LearnN.ODS v1.0 (c) Copyright 2024 Rob 
Hock *******"
'--------------------------------------- intresting stuff here
Const C_HolidaysMAX = 50            'Market holidays
Const C_LoadVarsOnce = FALSE        'Setting to TRUE may speed up 
content changes but changing vars on sheet will require sheet reload
Const C_FixupNETAfterDelete = TRUE  'NET column contains formula that 
references current row +1 which gives #REF error when deleting 
referenced row. FIXED!
Const C_PickaBagMsgDelay = 3        'Tell user to pick a bag every 1/x 
times cursor out of range

'-----------------------------------------------------------------
'  ContentChanged: SHEET EVENT trigger..Check for change to our
'                  BAGS in the SOLD column!
'-----------------------------------------------------------------
sub ContentChanged(oEvent)
        static hereb4%, bagsfromrow%, bagstorow%, historybeginrow%, 
blanktemplaterow%

'lookup our variables from sheet (speed increase unsure)
        If hereb4 = FALSE Then
    	    my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BagsFromRow)
    	    bagsfromrow = my_cell.Value
    	    my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BagsToRow)
    	    bagstorow = my_cell.Value
    	    my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_HistoryBeginRow)
    	    historybeginrow = my_cell.Value
    	    my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BlankTemplateRow)
    	    blanktemplaterow = my_cell.Value
    	    hereb4 = C_LoadVarsOnce
    	End If

'Need error handling in case range selected (oEvent.CellAddress 
won't be there!)
        On Error GoTo ErrorHandler    'maybe range selected!
  	
    	'Exit unless user changing a bag's sold price
    	If oEvent.CellAddress.Column <> C_ColumnSold Then Exit Sub
    	If oEvent.CellAddress.Row < (bagsfromrow-1) Then Exit Sub
    	If oEvent.CellAddress.Row > (bagstorow-1) Then Exit Sub
        StockSold
  ErrorHandler:
End Sub

'----------------------------------------------------------
'  Twits/SEC/Shorts/Yahoo/UserDefined:
'              BUTTON EVENTS trigger press..Shell our
'              internet URL's + stock ticker
'----------------------------------------------------------
Sub Twits
    ShellOurURL(C_URLtwits)
End Sub
Sub SEC
    ShellOurURL(C_URLsec)
End Sub
Sub Shorts
    ShellOurURL(C_URLshorts)
End Sub
Sub Yahoo
    ShellOurURL(C_URLyahoo)
End Sub
Sub UserDefined
    ShellOurURL(C_URLuser)
End Sub

'  ShellOurURL:  Parm1 is cell address ie. "A1" lookup containing URL to
 shell.
'                Stock ticker sourced from our current row & 
appended
'                to URL for final net address to shell execute. 
'------------------------------------------------------------------------------

Sub ShellOurURL(lookitup as string)
    dim ticker$, netaddress$
    ticker = OnYourTick
    If ticker = "" Then ticker = 
InputBox(C_MsgInputTick,C_MsgInputTickTitle)
    If ticker <> "" Then
	 	my_cell = ThisComponent.Sheets(0).getCellRangeByName(lookitup)
    	netaddress = my_cell.String & ticker
        Shelly(netaddress)
    Else
         PickaBag  'TELL USER TO PICK A BAG
    End If
End Sub

'-------------------------------------------
'  AboutUS:  BUTTON EVENT trigger press
'            Show dialog.. talk about us
'-------------------------------------------
Sub AboutUS
    MsgBox C_MsgAboutUS
    Shelly("https://robhock.net/learnN/")
End Sub

'------------------------------------------------------
'  PickaBag:  Notifies user to select a bag first
'------------------------------------------------------
Sub PickaBag
       static hereb4%
       Beep   'this is fun
       If hereb4 = 0 Then   'DONT BE TOTAL  BOTHER
       
           'PUT SELECTION ON OUR BAGS TO HIGHLIGHT THEM FOR USER
           dim bagsfromrow%, bagstorow%
           dim bagshere$   
           dim oSel
	       oSel = ThisComponent.getCurrentSelection()
           my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BagsFromRow)
           bagsfromrow = my_cell.Value
           my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BagsToRow)
           bagstorow = my_cell.Value
           bagshere = "$A$" & LTrim(Str(bagsfromrow)) & ":$Z$" 
& LTrim(Str(bagstorow))
           With ThisComponent.CurrentController
                .select(.ActiveSheet.getCellRangeByName(bagshere))
           End With
       
           'TELL USER TO PICK A BAG FIRST
           MsgBox C_MsgPickaBag,48,C_MsgPickaBagTitle
           
           'RESTORE CELL SELECTION
           With ThisComponent.CurrentController
                .select(oSel)
           End With
           
           'BEEP EVERYTIME, STOP & MSG 1/x TIMES
           hereb4 = C_PickaBagMsgDelay
       End If
       hereb4 = hereb4 - 1
End Sub

'----------------------------------------------------------------------
'  SortBags:  BUTTON EVENT Execute action (trigger)
'             Sorts our bags (current stock positions) by TICKER
'----------------------------------------------------------------------
sub SortBags
    'lookup our variables from sheet: bagsfromrow & bagstorow
    dim bagsfromrow%, bagstorow%        
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BagsFromRow)
    bagsfromrow = my_cell.Value
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BagsToRow)
    bagstorow = my_cell.Value

'need a dispatcher
    dim document   as object
    dim dispatcher as object
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

'select our range of bags
    dim args1(0) as new com.sun.star.beans.PropertyValue
    args1(0).Name = "ToPoint"
    args1(0).Value = "$A$" & LTrim(Str(bagsfromrow)) & ":$Z$" 
& LTrim(Str(bagstorow))
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args1())
       
    'setup for sorting
	dim args2(12) as new com.sun.star.beans.PropertyValue
    args2(0).Name = "ByRows"
    args2(0).Value = true
    args2(1).Name = "HasHeader"
    args2(1).Value = false
    args2(2).Name = "CaseSensitive"
    args2(2).Value = false
    args2(3).Name = "NaturalSort"
    args2(3).Value = false
    args2(4).Name = "IncludeAttribs"
    args2(4).Value = true
    args2(5).Name = "UserDefIndex"
    args2(5).Value = 0
    args2(6).Name = "Col1"
    args2(6).Value = C_ColumnDTLeft
    args2(7).Name = "Ascending1"
    args2(7).Value = true
    args2(8).Name = "Col2"
    args2(8).Value = C_ColumnTicker
    args2(9).Name = "Ascending2"
    args2(9).Value = true
    'args2(10).Name = "Col3"
    'args2(10).Value = 4
    'args2(11).Name = "Ascending3"
    'args2(11).Value = true
    'args2(12).Name = "IncludeComments"
    'args2(12).Value = false

'SORT IT!
    dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, 
args2())
end sub

'----------------------------------------------------------------------
'  OnYourTick:  =returns String stock ticker on our current row or ""
'          todo: consolidate this and OnYourBroker()
'----------------------------------------------------------------------
Function OnYourTick As String
	Dim oDoc, oSel
    dim col%, row%
	dim ticker$
	ticker = ""

'Get current selection
	oDoc = ThisComponent
	oSel = oDoc.getCurrentSelection()
	
	If oSel.supportsService("com.sun.star.sheet.SheetCell") Then
        oConv = 
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
        oConv.Address = oSel.getCellAddress
		col = oConv.Address.Column
    	row = oConv.Address.Row
		
		'Get ticker on our row at our column
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnTicker,row)
    	ticker = my_cell.getString()
    End If

OnYourTick = ticker
End Function

'-----------------------------------------------------------------------
'  OnYourBroker: =returns String broker code for row cursor is on or ""
'          todo: consolidate this and OnYourTicker()
'-----------------------------------------------------------------------
Function OnYourBroker As String
	Dim oDoc, oSel
    dim col%, row%
	dim broker$
	broker = ""

'GET CURRENT SELECTION
	oDoc = ThisComponent
	oSel = oDoc.getCurrentSelection()
	
	If oSel.supportsService("com.sun.star.sheet.SheetCell") Then
        oConv = 
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
        oConv.Address = oSel.getCellAddress
		col = oConv.Address.Column
    	row = oConv.Address.Row
		
		'Save broker code
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,row)
    	broker = my_cell.getString()
    End If

OnYourBroker = broker
End Function

'------------------------------------------------------------------------

'  BrokerLookup: =returns COLUMN +Parm2 for broker code passed in Parm1
'                 Parm1 = text broker code to lookup
'                 Parm2 = (OPTIONAL) index for desired column data
'------------------------------------------------------------------------

Function BrokerLookup(brkcode$, Optional brkindex%) As Variant
    'practice our optional parameters
    dim idx%
    idx = 1
    If Not IsMissing (brkindex) Then idx = brkindex
    
    'now use broker code to lookup the name  (+1 COLUMN)
    dim brokershere$, thiscode$
    dim row%, col%
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BrokersHere)
    brokershere = my_cell.getString()
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(brokershere)
    my_cell_addr = my_cell.getCellAddress()
    row =  my_cell_addr.Row
    col =  my_cell_addr.Column
    
    thiscode = "Hi Mom!"
    While thiscode <> ""
   	    my_cell = ThisComponent.Sheets(0).getCellByPosition(col,row)
   	    thiscode = my_cell.getString()
   	    If thiscode = brkcode Then
   	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(col+idx,row)
   	        BrokerLookup = my_cell.getString()
   	        thiscode =""
   	    End If
        row = row+1
     Wend
End Function

'---------------------------------------------------------------------
'  SetDTLeft:  Parm1 = number daytrades to set on our current row
'       todo:  rename, repurpose to write field to current row
'---------------------------------------------------------------------
Sub SetDTLeft( howmany% )
	Dim oDoc, oSel
    dim col%, row%

'Get current selection
	oDoc = ThisComponent
	oSel = oDoc.getCurrentSelection()
	
	If oSel.supportsService("com.sun.star.sheet.SheetCell") Then
        oConv = 
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
        oConv.Address = oSel.getCellAddress
		col = oConv.Address.Column
    	row = oConv.Address.Row
		
		'Set DT Left
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDTLeft,row)
 		my_cell.Value = howmany 	
    End If
End Sub

'----------------------------------------------------------------
'  IsThisDayTrade:  =returns TRUE/FLASE if date found in array
'                   Parm1 = Date to look for
'                   Parm2 = array of Dates(5) base 1
'----------------------------------------------------------------
Function IsThisDayTrade( tdate As Date, last5tradingdays As Object)
    dim r as boolean
    r=FALSE
    dim i%
    i=0
    While i<5
        i = i+1
        If last5tradingdays(i) = tdate Then r=TRUE
    Wend
    IsThisDayTrade = r
End Function

'-----------------------------------------------------------------------
'  Daytrades: BUTTON EVENT execute action trigger...
'             Displays dialog for broker code found on current row
'             populated with last 3 daytrades and number remaining.
'-----------------------------------------------------------------------
Sub Daytrades
    'load our holidays into arrays: holiday & holidayname (base 1 
through holidays)
    dim holidayswhere$
    dim holiday(C_HolidaysMAX) as date
    dim holidayname(C_HolidaysMAX) as string
    dim holidays%, hrow%, hcol%
    holidays = 0
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_HolidaysHere)
    holidayswhere = my_cell.getString()
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(holidayswhere)
    my_cell_addr = my_cell.getCellAddress()
    hrow =  my_cell_addr.Row
    hcol =  my_cell_addr.Column
    Do
   	    holidays = holidays+1
   	    my_cell = ThisComponent.Sheets(0).getCellByPosition(hcol,hrow)
   	    holiday(holidays) = my_cell.Value()
   	    my_cell = ThisComponent.Sheets(0).getCellByPosition(hcol+1,hrow)
   	    holidayname(holidays) = my_cell.getString()
   	    hrow = hrow+1
  	Loop Until holiday(holidays) =0
   	holidays = holidays -1      'makes sense to me
    
    'calculate last5tradedays(1-5) array with last 5 trading days 
    dim todaydate as date
    dim last5tradedays(5) as date
    dim last5count%, mycount%, myflag%, dayoweek%
    dim myholiday$, todaystr$
    
    'todaydate/str = today's date (easier way to do this)
    fa = createUnoService("com.sun.star.sheet.FunctionAccess")
    todaydate = fa.callFunction("TODAY",Array( ))
  
    'Fill our last5tradedays array
    last5count = 0
    myholiday = ""        'if we trip over holiday mention it
    While last5count <5
       'is this date a trading day? (1=sun, 2=mon, 3=tue, 4=wed. 5=thur.
 6=fri, 7=sat)
        todaystr = todaydate
        args = Array( todaystr )
        dayoweek = fa.callFunction("WEEKDAY",args)
        If dayoweek >1 Then
           If dayoweek <7 Then
              'make sure not holiday
               mycount = 1          
               myflag = FALSE     
               While mycount <= holidays
                    If  todaydate = holiday(mycount) Then
                        myflag = TRUE
                        'save name to show user we tripped on holiday
                        myholiday = "("+C_StrHOLIDAY+": 
"+holidayname(mycount)+")"
                    End If
                    mycount = mycount +1
               Wend
                If myflag = FALSE Then  
                    last5count = last5count +1
                    last5tradedays(last5count) = todaydate
                End If
           End If
        End If
        'move to day before
        todaydate = todaydate - 1
    Wend
    
    'get last 3 daytrades for this broker (Handles instance when <3 
also)
    dim broker$, brokername$, brokerpnl$
    broker = OnYourBroker
    brokername = BrokerLookup(broker,C_BrokersColumnName)
    brokerpnl = BrokerLookup(broker,C_BrokersColumnPNL)
    dim row%   'row our history at
    my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_HistoryBeginRow)
    row = my_cell.Value -1   'make base 0

If broker <> "" Then
	 	dim document   as object
		dim dispatcher as object
    	dim args2(0)   as new com.sun.star.beans.PropertyValue
		document   = ThisComponent.CurrentController.Frame
		dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

'Loop through history for match to our broker
  	    dim thistradedatewas as date
	    dim thisbroker$, thistradewas$
	    dim matches%
	    thisbroker = ""
	    thistradewas = ""
	    matches = 0
	    dim trades(3) as string
	    trades(1) = ""
	    trades(2) = ""
	    trades(3) = ""
	    dim tradedates(3) as date
	    tradedates(1) = ""
	    tradedates(2) = ""
	    tradedates(3) = ""
	    dim tradestat(3) as integer
	    tradestat(1) = FALSE
	    tradestat(2) = FALSE
	    tradestat(3) = FALSE
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,row)
    	thisbroker = my_cell.getString()
  
        While thisbroker <> ""
            If thisbroker = broker Then
        	    my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDTStatus,row)
    	        thistradewas = my_cell.getString()
   	            my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDate,row)
                thistradedatewas = my_cell.Value()

If thistradewas = C_StrDAYTRADE Then
                    matches = matches +1
        	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnTicker,row)
                    trades(matches) = my_cell.getString()
        	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDate,row)
                    tradedates(matches) = my_cell.getString()   
                End If
            End If
  
            row = row +1
	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,row)
            thisbroker = my_cell.getString()
 
            'If all our matches in, show/populate dialog
            If matches = 3 OR thisbroker="" Then 
               thisbroker = ""
               With GlobalScope.Basiclibraries
               If Not .IsLibraryLoaded("Tools") Then 
.LoadLibrary("Tools")
	           End With
	           oDialog1 = Tools.LoadDialog("Standard", 
"daytrades",ThisComponent.DialogLibraries)
    	       oDialog1Model = oDialog1.Model
    	       oDialog1.Title = "Calculate Day Trades Remaining"
    	       oLabel1 = oDialog1.GetControl("broker")
    	       oLabel1.Text = "(" & broker & ")  " & brokername
 & " = " & brokerpnl
    	       oLabel1 = oDialog1.GetControl("warning")
    	       oLabel1.Text = BrokerLookup(broker,C_BrokersColumnNotes4DT)
    	       oLabel1 = oDialog1.GetControl("trade1")
    	       oLabel1.Text = trades(1)
    	       oLabel1 = oDialog1.GetControl("trade2")
    	       oLabel1.Text = trades(2)
    	       oLabel1 = oDialog1.GetControl("trade3")
    	       oLabel1.Text = trades(3)
 
    	       oLabel1 = oDialog1.GetControl("tradedate1")
    	       oLabel1.Text = tradedates(1)
    	       oLabel1 = oDialog1.GetControl("tradedate2")
    	       oLabel1.Text = tradedates(2)
    	       oLabel1 = oDialog1.GetControl("tradedate3")
    	       oLabel1.Text = tradedates(3)

dim dtleft%
               dtleft = 3
    	       oLabel1 = oDialog1.GetControl("label1")
    	       oLabel1.Text = C_StrOUT
    	       If IsThisDayTrade( tradedates(1) , last5tradedays ) Then 
    	          oLabel1.Text = "#1> "+C_StrDAYTRADE
    	          dtleft = dtleft -1 
    	       End If
    	       oLabel1 = oDialog1.GetControl("label2")
    	       oLabel1.Text = C_StrOUT
    	       If IsThisDayTrade( tradedates(2) , last5tradedays ) Then
    	          oLabel1.Text = "#2> "+C_StrDAYTRADE
    	          dtleft = dtleft -1 
    	       End If    
    	       oLabel1 = oDialog1.GetControl("label3")
    	       oLabel1.Text = C_StrOUT
    	       If IsThisDayTrade( tradedates(3) , last5tradedays ) Then 
    	          oLabel1.Text = "#3> "+C_StrDAYTRADE
    	          dtleft = dtleft -1
    	       End If 
 
   	           oLabel1 = oDialog1.GetControl("message")
   	           oLabel1.Text = dtleft & C_StrDayRem & myholiday
   	           oLabel1 = oDialog1.GetControl("DTLeft")
   	           oLabel1.Text = dtleft

'execute dialog, if OK pressed, set the new count
    	       dim dlgret%
    	       dlgret = oDialog1.Execute()
               If dlgret = 1 Then SetDTLeft( dtleft )
            End If
        Wend

Else
        PickaBag   'TELL USER TO PICK A BAG FIRST
    End If
End Sub

'---------------------------------------------------
'  DeleteHistory_OKpwrtoo: BUTTON event press
'                     Set a FLAG, end our dialog
'---------------------------------------------------
Sub DeleteHistory_OKpwrtoo(oEvent)
    oDlg = oEvent.source.Context
    oC1 = oDlg.GetControl("resetpwr")   'THIS CONTROL HIDDEN
    oC1.State = TRUE
    oDlg.EndExecute()
End Sub

'------------------------------------------------------------------------

'  DeleteHistory: BUTTON EVENT execute action trigger on press...
'                 Display dialog & allow user to delete select 
history
'------------------------------------------------------------------------

Sub DeleteHistory
    'get our component & sheet & ready
	dim oDoc, oSheet
	oDoc = ThisComponent
    oSheet = oDoc.Sheets(0)

dim blanktemplaterow%, historybeginrow%, lastrow%, row%, cx%, 
brokerstotal%, tickerstotal%
    dim broker$, ticker$, formula$
    dim datethis as Date	
    dim datefrom as Date
    dim dateto as Date
    
    'where is blank template row?, where History?
   	my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BlankTemplateRow)
   	blanktemplaterow = my_cell.Value
 	my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_HistoryBeginRow)
   	historybeginrow = (my_cell.Value) -1    'BASE 0 PLEASE
    row = historybeginrow

'where our last row?
    lastrow = GetLastRow()

'Exit if nothing to delete else show IamWorking
    If lastrow < historybeginrow Then Exit Sub
    IamWorking(TRUE)

'Fill our arrays: brokers() & tickers() & get dates hi/lo
    dim brokers$(0 To lastrow-historybeginrow+1)
    dim tickers$(0 To lastrow-historybeginrow+1)
    cx = 0
    brokerstotal = 0
    tickerstotal = 0
    datefrom = date()  'Must init this one
    While cx <= (lastrow - historybeginrow)
      'broker-------
 	    my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,historybeginrow+cx)

broker = my_cell.getString()
        If NOT FindStringInArray(broker, brokers) Then
           brokers(brokerstotal)= my_cell.getString()
           brokerstotal = brokerstotal +1
        End If
     'ticker-------
 	    my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnTicker,historybeginrow+cx)

ticker = my_cell.getString()
        If NOT FindStringInArray(ticker, tickers) Then
           tickers(tickerstotal)= my_cell.getString()
           tickerstotal = tickerstotal +1
        End If
     'trade date-------
 	    my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDate,historybeginrow+cx)

datethis = my_cell.Value
        if datethis > 0 AND datethis < datefrom Then datefrom = 
datethis
        if datethis > dateto Then dateto = datethis
        
      '-------next record
        cx = cx +1
    Wend
    
    'Re-dimension and alphabetize our string arrays
    ReDim preserve brokers$(brokerstotal)
    ReDim preserve tickers$(tickerstotal)
    ShellSort(tickers)
    ShellSort(brokers)
    Iamworking(FALSE)
        
    'NOW SHOW OUR DIALOG
    With GlobalScope.Basiclibraries
	If Not .IsLibraryLoaded("Tools") Then .LoadLibrary("Tools")
	End With
	oDialog1 = Tools.LoadDialog("Standard", 
"deletehistory",ThisComponent.DialogLibraries)
    oDialog1Model = oDialog1.Model
    oDialog1.Title = C_MsgDeleteHistoryTitle
  	
	'populate brokers & tickers
    cmbBox1 = oDialog1.GetControl("broker")
    cmbBox1.addItems(brokers,1)
    cmbBox1 = oDialog1.GetControl("ticker")
    cmbBox1.addItems(tickers,1)
    
    'populate date fields from/to
    oDateField = oDialog1.GetControl("datefrom")
    oDateField.Text = datefrom
    oDateField = oDialog1.GetControl("dateto")
    oDateField.Text = dateto
     
    'execute dialog, if OK pressed, do the Delete
    dim dlgret%, picked%, deletetotal%, currentlastrow%
    dim deleterow As boolean, resetpwr As boolean
    dim thisbroker$, thisticker$, thatformula$, pwrref$, srcrange$
    dim pnl#, pwr#
    dlgret = oDialog1.Execute()
    oC1 = oDialog1.GetControl("resetpwr")   'See if user pressed OK or 
"OK +Reset Pwr"
    resetpwr = oC1.State
    If dlgret = 1 OR resetpwr = TRUE Then
       
        'I am WORKING + Disable document recalc to speed things up
        IamWorking(TRUE)
        oDoc.lockControllers()
        oDoc.addActionLock()
        'oDoc.enableAutomaticCalculation(FALSE)    'BAD IDEA
              
        'case selected sort to: picked
        picked = 0
        oC1 = oDialog1.GetControl("pickbrokereq")
        If oC1.State = TRUE Then picked =1
        oC1 = oDialog1.GetControl("pickbrokerneq")
        If oC1.State = TRUE Then picked =2
        oC1 = oDialog1.GetControl("picktickereq")
        If oC1.State = TRUE Then picked =3
        oC1 = oDialog1.GetControl("picktickerneq")
        If oC1.State = TRUE Then picked =4
        oC1 = oDialog1.GetControl("pickdateeq")
        If oC1.State = TRUE Then picked =5
        oC1 = oDialog1.GetControl("pickdateneq")
        If oC1.State = TRUE Then picked =6
        oC1 = oDialog1.GetControl("pickall")
        If oC1.State = TRUE Then picked =7
        oC1 = oDialog1.GetControl("picklast")
        If oC1.State = TRUE Then picked =8

'Get our values we're testing for
        oC1 = oDialog1.GetControl("broker")
        broker = oC1.Model.Text        
        oC1 = oDialog1.GetControl("ticker")
        ticker = oC1.Model.Text        
        oC1 = oDialog1.GetControl("datefrom")
        datefrom = CDate(oC1.Model.Text)
        oC1 = oDialog1.GetControl("dateto")
        dateto = CDate(oC1.Model.Text)
       
        'Let us delete from last to first
        cx = 0
        deletetotal = 0
        While cx <= (lastrow - historybeginrow)
            'read in stuff we're testing (need pwrref & pnl if 
resetting pwr)
 	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,lastrow-cx)
            thisbroker = my_cell.getString()
 	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnTicker,lastrow-cx)
            thisticker = my_cell.getString()
 	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDate,lastrow-cx)
            datethis = my_cell.Value
	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnPwrRef,lastrow-cx)
            pwrref = my_cell.getString()
            my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnEarned,lastrow-cx)
            pnl = my_cell.Value
	  
            my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnNET,lastrow-cx-1)
            thatformula = my_cell.Formula   'FORMULA FOR NET COLUMN 
ABOVE OUR ROW
   
            deleterow = FALSE     'assume keeping
            Select Case picked
                Case 1
                    If thisbroker = broker Then deleterow = TRUE
                Case 2
                    If thisbroker <> broker Then deleterow = TRUE
                Case 3
                    If thisticker = ticker Then deleterow = TRUE
                Case 4
                    If thisticker <> ticker Then deleterow = TRUE
                Case 5
                    If datethis  >= datefrom AND datethis <= 
dateto Then deleterow = TRUE
                Case 6
                    If datethis  < datefrom OR datethis > dateto 
Then deleterow = TRUE
                Case 7
                     deleterow = TRUE
                Case 8
                    If (lastrow - cx) = historybeginrow Then deleterow =
 TRUE
            End Select
            If thisticker = "" Then deleterow = TRUE   'DELETE OUR 
BLANKS

'DO THE DELETE-------THEN NEXT...
            If deleterow = TRUE Then

'Reset record's Power addition if asked (sub p&l 
from broker's balance (pwrref))
                If resetpwr = TRUE AND pwrref <> "" Then
                    my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(pwrref)
     	            pwr = my_cell.Value
                    my_cell.Value = Round(pwr - pnl,2)    'lets round to
 2 decimal places
                End If
                   
                'DELETE IT! (but not first 2 rows!! Chart is 
watching/will mess up! Add blank row at end 1st)
                If GetLastRow() <= historybeginrow+1 Then
                   oSheet.Rows.insertByIndex(GetLastRow()+1,1)
                End If

'Now DELETE IT! 
                oSheet.rows.removeByIndex(lastrow-cx, 1)
                If C_FixupNETAfterDelete = TRUE Then
                   my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnNET,lastrow-cx-1)
                   If my_cell.Formula <> "" Then my_cell.Formula =
 thatformula   'COPY FORMULA BACK ABOVE US
                End If
                   
                'Count it unless it was just blank row we padded long 
ago
                If thisticker <> "" Then deletetotal = deletetotal
 +1
            End If     
            cx = cx +1
        Wend
         
        'FIXUP our chart data source range
        ChartResetSource
        
        'RE-Enable document recalc
        'oDoc.enableAutomaticCalculation(TRUE)
        oDoc.removeActionLock()
        oDoc.unlockControllers()
        IamWorking(FALSE)
        MsgBox C_StrDELETED & deletetotal & C_StrTRADES
    End If
End Sub

'-------------------------------------------------------------------------------------

'  ChartResetSource:  Resets our chart's data source range after History
 modified
'-------------------------------------------------------------------------------------

Sub ChartResetSource
    dim blanktemplaterow%, historybeginrow%, lastrow%
    dim RangeName$
    dim RangeCells As Object
    dim RangeAddress As New com.sun.star.table.CellRangeAddress
    dim oDoc, oSheet
    oDoc = ThisComponent
    oChart = oDoc.Sheets(0).Charts(0)
    
    'get our row #'s
    my_cell = oDoc.Sheets(0).getCellRangeByName(C_BlankTemplateRow)
    blanktemplaterow = my_cell.Value
    my_cell = oDoc.Sheets(0).getCellRangeByName(C_HistoryBeginRow)
    historybeginrow = (my_cell.Value) -1    'BASE 0 PLEASE
    lastrow = GetLastRow()

oChart = oDoc.Sheets(0).Charts(0)
    Ranges = oChart.Ranges
    Ranges(0).StartRow = historybeginrow -1   'MUST INCLUDE HEADER ROW
    Ranges(0).EndRow = lastrow
    oChart.setRanges(Ranges)
End Sub

'--------------------------------------------------
'  IamWorking:  Shows "BUSY" modeless dialog.
'               Parm1 = TRUE/FALSE to show/hide
'--------------------------------------------------
Sub IamWorking(yesno As Boolean)
    static oDlgWorking As Object
	If yesno = TRUE Then 
	   With GlobalScope.Basiclibraries
       If Not .IsLibraryLoaded("Tools") Then .LoadLibrary("Tools")
       End With
       oDlgWorking = Tools.LoadDialog("Standard", 
"iamworking",ThisComponent.DialogLibraries)
       oDlgWorking.Title = C_MsgWorkingTitle
       oDlgWorking.setVisible(yesno)
    End If
    If yesno = FALSE Then
        oDlgWorking.setVisible(yesno)
       'oDlgWorking.Terminate()
    End If
End Sub

'----------------------------------------------------------
'  GetLastRow:  =row # of last row
'----------------------------------------------------------
Function GetLastRow() As Integer
	Dim oDoc, oSheet, oCursor
	oDoc = ThisComponent
    oSheet = oDoc.Sheets(0)
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(False)
	oCursor.gotoStartOfUsedArea(True)
	'oCursor.collapseToCurrentRegion()   'DON'T DO IT! WE'RE PADDING ROWS 
FOR CHART
	GetLastRow = oCursor.getRangeAddress().EndRow
End Function

'----------------------------------------------------------
'  FindStringInArray:  =TRUE/FALSE string found in array
'                      Parm1 = string
'                      Parm2 = array
'----------------------------------------------------------
Function FindStringInArray(findstr As String, ByRef aArray As String) As
 Boolean
    'GlobalScope.BasicLibraries.loadLibrary("Tools")
    With GlobalScope.Basiclibraries
	If Not .IsLibraryLoaded("Tools") Then .LoadLibrary("Tools")
	End With
    FindStringInArray = Tools.FieldInArray(aArray, UBound(aArray), 
findstr)
End Function

'------------------------------------------------------------
'  InputBags: BUTTON EVENT execute action trigger press...
'             Displays dialog to edit bag CURSOR ON
'------------------------------------------------------------
Sub InputBags
    dim row%, col%
    dim broker$, ticker$, shares$, bot$, sold$, comments$
   	broker = ""
   	ticker = ""
   	shares = ""
   	bot = ""
   	sold = ""
   	comments = ""
   	'dim shares%, bot%, sold%  'maybe should use double?

'get current selection
	dim oDoc, oSel
	oDoc = ThisComponent
	oSel = oDoc.getCurrentSelection()
	
	If oSel.supportsService("com.sun.star.sheet.SheetCell") Then
	    oConv = 
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
        oConv.Address = oSel.getCellAddress
		col = oConv.Address.Column
    	row = oConv.Address.Row
    	
        'lookup our variables from sheet: bagsfromrow & bagstorow, 
historybeginrow & blanktemplaterow
        dim bagsfromrow%, bagstorow%, historybeginrow%, 
blanktemplaterow%
    	my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BagsFromRow)
    	bagsfromrow = my_cell.Value
    	my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BagsToRow)
    	bagstorow = my_cell.Value
    	my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_HistoryBeginRow)
    	historybeginrow = my_cell.Value
    	my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BlankTemplateRow)
    	blanktemplaterow = my_cell.Value
   	
    	'See if we are on a BAG, else tell user to pick one
    	If row < (bagsfromrow-1) OR row > (bagstorow-1) Then
    	    PickaBag
    	    Exit Sub
    	End If
  
        'read in from sheet our broker, ticker, # shares, bot price
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,row)
    	broker = my_cell.getString()
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnTicker,row)
    	ticker = my_cell.getString()
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnShares,row)
        shares = my_cell.getString()
	 	my_cell = ThisComponent.Sheets(0).getCellByPosition(C_ColumnBot,row)
        bot = my_cell.getString()
	 	my_cell = ThisComponent.Sheets(0).getCellByPosition(C_ColumnSold,row)
        sold = my_cell.getString()
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnComments,row)
        comments = my_cell.getString()
	Else
		If oSel.supportsService("com.sun.star.sheet.SheetCellRange") Then
		    'ONE CELL SELECTED
		Else
		    If oSel.supportsService("com.sun.star.sheet.SheetCellRanges") Then
				'MANY CELLS SELECTED: oSel.getCount()
		    Else
			    'BARK BARK WRONG TREE
			End If
		End If
		Exit Sub
	End If	
 
    'NOW SHOW OUR DIALOG
    With GlobalScope.Basiclibraries
	If Not .IsLibraryLoaded("Tools") Then .LoadLibrary("Tools")
	End With
	oDialog1 = Tools.LoadDialog("Standard", 
"inputbags",ThisComponent.DialogLibraries)
    oDialog1Model = oDialog1.Model
    oDialog1.Title = C_MsgInputBagsTitle
 
    'populate our broker combo box
    dim brokershere$, thiscode$
    dim cx%, brow%, bcol%
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BrokersHere)
    brokershere = my_cell.getString()
    my_cell = ThisComponent.Sheets(0).getCellRangeByName(brokershere)
    my_cell_addr = my_cell.getCellAddress()
    brow =  my_cell_addr.Row
    bcol =  my_cell_addr.Column
    cx = 0
    cmbBox1 = oDialog1.GetControl("broker")
    If  cmbBox1.getItemCount = 0 then
        thiscode = "Hi Dad!"  
        While thiscode <> ""
   	        my_cell = 
ThisComponent.Sheets(0).getCellByPosition(bcol,brow+cx)
   	        thiscode = my_cell.getString()
            cmbBox1.addItem(thiscode,cx+1)
            cx = cx +1
         Wend
    End If
	cmbBox1.Text = broker

'populate other stuff, choose focus
    oC1 = oDialog1.GetControl("comments")
	oC1.Text = comments
	oView = oC1.getView()     'we need a view
	
    oC1 = oDialog1.GetControl("sold")
	oC1.Text = sold
	If sold = "" Then oView = oC1.getView()

oC1 = oDialog1.GetControl("bot")
	oC1.Text = bot
	If  bot = "" Then oView = oC1.getView()
	
    oC1 = oDialog1.GetControl("shares")
	oC1.Text = shares
	If shares = "" Then oView = oC1.getView()
	
    oC1 = oDialog1.GetControl("ticker")
	oC1.Text = ticker
	If ticker = "" Then oView = oC1.getView()
	
    oC1 = oDialog1.GetControl("broker")
	If broker = "" Then oView = oC1.getView()

'Choose focus
    oView.setFocus()
	
    'execute dialog, if OK pressed & SOLD, BAG is HISTORY
    dim dlgret%
    dlgret = oDialog1.Execute()
    If dlgret = 1 Then
        'Disable document recalc to speed things up
        oDoc.lockControllers()
        oDoc.addActionLock()

'read in dialog values
        dim Nbroker$, Nticker$, Nshares$, Nbot$, Nsold$, Ncomments$
        oC1 = oDialog1.GetControl("broker")
        Nbroker = oC1.Text
        oC1 = oDialog1.GetControl("ticker")
        Nticker = oC1.Text
        oC1 = oDialog1.GetControl("shares")
        Nshares = oC1.Text
        oC1 = oDialog1.GetControl("bot")
        Nbot = oC1.Text
        oC1 = oDialog1.GetControl("sold")
        Nsold = oC1.Text
        oC1 = oDialog1.GetControl("comments")
        Ncomments = oC1.Text

'write out to sheet: broker, ticker, # shares, bot, comments
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,row)
    	my_cell.String = Nbroker
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnTicker,row)
    	my_cell.String = Nticker
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnComments,row)
    	my_cell.String = Ncomments
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnShares,row)
        my_cell.Value = Val(Nshares)
        If Val(Nshares) = 0 Then
           my_cell.clearContents(15)  '15 binary is 1111 
(VALUE/DATE/STR/ANNOTATION)
        End If
	 	my_cell = ThisComponent.Sheets(0).getCellByPosition(C_ColumnBot,row)
        my_cell.Value = Val(Nbot)
        If Val(Nbot) = 0 Then
           my_cell.clearContents(15)  'delete instead of writing 0
        End If
        If LTrim(Nsold) <> "" Then
     	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnSold,row)
            my_cell.Value = Val(Nsold)
            StockSold
        End If

'RE-Enable document recalc
    oDoc.removeActionLock()
    oDoc.unlockControllers()
    End If
End Sub

'--------------------------------------------------------------------------

'  InputBagsBrokerNew: DIALOG CONTROL EVENT Content Changed (trigger)
'                      Update other controls to reflect new broker 
select
'--------------------------------------------------------------------------

Sub InputBagsBrokerNew(oEvent)
    dim broker$, brokername$, brokerpnl$, brokernote$
    broker = oEvent.source.Model.Text
    brokername = BrokerLookup(broker,C_BrokersColumnName)
    brokerpnl = Str(BrokerLookup(broker,C_BrokersColumnPNL))
    brokernote = BrokerLookup(broker,C_BrokersColumnNotes4Input)
    oEvent.Source.Model.HelpText = brokername & " = " & 
brokerpnl
    oDlg = oEvent.source.Context
    oDlg.Title = C_MsgInputBagsTitle & " --- " & brokername
    oLabel1 = oDlg.GetControl("power")
    oLabel1.Text = brokerpnl
    oLabel1 = oDlg.GetControl("brokername")
    oLabel1.Text = brokername
    oLabel1 = oDlg.GetControl("brokernote")
    oLabel1.Text = brokernote
    InputBagsChange(oEvent)
End Sub

'----------------------------------------------------------------------------------

'  InputBagsChange:  DIALOG CONTROL EVENT Content Changed (trigger)
'                    Update our progress bar with new power, recalc 
spent, earned
'----------------------------------------------------------------------------------

Sub InputBagsChange(oEvent)
    dim brokerpnl$, pwr#, bot#, sold#, shares#, spent$, earned$, 
percent#
    percent = 100
    spent = ""
    earned = ""
    oDlg = oEvent.source.Context

'Read in values we need
    oLabel1 = oDlg.GetControl("sold")
    sold = Val(oLabel1.Text)
    oLabel1 = oDlg.GetControl("bot")
    bot = Val(oLabel1.Text)
    If bot <> 0 Then
        oLabel1 = oDlg.GetControl("power")
        brokerpnl = oLabel1.Text
        pwr = Val(brokerpnl)
        oLabel1 = oDlg.GetControl("shares")
        shares = Val(oLabel1.Text)

'here it is
        If pwr <> 0 Then percent = 100-((bot*shares*100) / pwr)
        spent = "=" & Format(shares*bot,"0.00")
        If sold <> 0 Then earned = "=" & 
Format(shares*sold,"0.00") & " earned " & 
Format((sold*shares)-(bot*shares),"0.00")
    End If
  
    'Write out dollars spent/earned to static label controls
    oLabel1 = oDlg.GetControl("spent")
    oLabel1.Text = spent
    oLabel1 = oDlg.GetControl("earned")
    oLabel1.Text = earned
   
    'Set our progress bar to reflect percent power using
    oProg1 = oDlg.GetControl("percentpwr")
    oProg1.setValue(percent)
End Sub

'-----------------------------------------------------------------
'  InputBagsReset:  DIALOG CONTROL EVENT Button pressed trigger
'                   Erase contents of all fields (reset)
'-----------------------------------------------------------------
Sub InputBagsReset(oEvent)
    oDlg = oEvent.source.Context
    oLabel1 = oDlg.GetControl("ticker")
    oLabel1.Text = ""
    oLabel1 = oDlg.GetControl("shares")
    oLabel1.Text = ""
    oLabel1 = oDlg.GetControl("bot")
    oLabel1.Text = ""
    oLabel1 = oDlg.GetControl("sold")
    oLabel1.Text = ""
    oLabel1 = oDlg.GetControl("comments")
    oLabel1.Text = ""
    InputBagsChange(oEvent)
End Sub

'----------------------------------------------------------------
'  InputBagsBtn1-4:  DIALOG CONTROL EVENT Button pressed trigger
'                    Calc/set shares to x/4 power
'----------------------------------------------------------------
Function InputBagsBtn1to4(oEvent, divideby#) As Integer
    dim pwr#, bot#, nshr#
    oDlg = oEvent.source.Context
    oLabel1 = oDlg.GetControl("power")
    pwr = Val(oLabel1.Text)
    oLabel1 = oDlg.GetControl("bot")
    bot = Val(oLabel1.Text)
    If bot<>0 Then  
       'nshr = Round(pwr/bot*divideby)                  'this is VBA 
func
        nshr = Val(Format(Str(pwr/bot*divideby),"0"))   'maybe use 
Format to round
        oLabel1 = oDlg.GetControl("shares")
        oLabel1.SetValue (nshr)
    End If
    InputBagsChange(oEvent)   'We changed stuff
    InputBagsBtn1to4 = nshr    'Return for fun
End Function

Sub InputBagsBtn1(oEvent)
    dim rval%
    rval = InputBagsBtn1to4(oEvent,0.25)
End Sub
Sub InputBagsBtn2(oEvent)
    dim rval%
    rval = InputBagsBtn1to4(oEvent,0.50)
End Sub
Sub InputBagsBtn3(oEvent)
    dim rval%
    rval = InputBagsBtn1to4(oEvent,0.75)
End Sub
Sub InputBagsBtn4(oEvent)
    dim rval%
    rval = InputBagsBtn1to4(oEvent,0.99)          'note 100% may round 
up to too many shares
End Sub

'---------------------------------------------------------------------------------

'   StockSold:  Move SELECTED record to history, replace with BLANK 
TEMPLATE ROW
'               adjust our P&L for broker, -1 DT if needed
'---------------------------------------------------------------------------------

Sub StockSold
	Dim oDoc, oSel
    dim col%, row%

'Get current selection
	oDoc = ThisComponent
	oSel = oDoc.getCurrentSelection()
	
	If oSel.supportsService("com.sun.star.sheet.SheetCell") Then
        'Disable document recalc to speed things up
        oDoc.lockControllers()
        oDoc.addActionLock()

oConv = 
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
        oConv.Address = oSel.getCellAddress
		col = oConv.Address.Column
    	row = oConv.Address.Row
    	
        'lookup our variables from sheet: bagsfromrow & bagstorow, 
historybeginrow & blanktemplaterow
        dim bagsfromrow%, bagstorow%, historybeginrow%, 
blanktemplaterow%
    	my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BagsFromRow)
    	bagsfromrow = my_cell.Value
    	my_cell = ThisComponent.Sheets(0).getCellRangeByName(C_BagsToRow)
    	bagstorow = my_cell.Value
    	my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_HistoryBeginRow)
    	historybeginrow = my_cell.Value
    	my_cell = 
ThisComponent.Sheets(0).getCellRangeByName(C_BlankTemplateRow)
    	blanktemplaterow = my_cell.Value

dim broker$, pwrref$
		dim pnl#, pwr#   'declare as doubles.. currency has issues
		
		'Save BROKER
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,row)
    	broker = my_cell.getString()

'Get PWRREF (address of cell containing power balance for our 
broker)
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnPwrRef,row)
    	pwrref = my_cell.getString()

'pnl = EARNED 
   		my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnEarned,row)
    	pnl = my_cell.Value

'Add P&L to this broker's balance (PWRREF)
    	my_cell = ThisComponent.Sheets(0).getCellRangeByName(pwrref)
    	pwr = my_cell.Value
    	my_cell.Value = Round(pwr + pnl,2)    'lets round to 2 decimal 
places
        'my_cell.Value = Val(Format(Str(pwr + pnl),"0.00"))   'use 
Format to Round()
 
        'Get/Save our DT's remaining and clear cell
        dim dtleft$
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDTLeft,row)
    	dtleft = my_cell.getString()
    	my_cell.String = ""

'Get/Save DAYTRADE status
        dim dtstatus$
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDTStatus,row)
    	dtstatus = my_cell.getString()

'Make SHARES static (delete possible formula, keep the value)
        dim shares%
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnShares,row)
    	shares = my_cell.Value
    	my_cell.Value = shares

'Cut our record and paste it to history
        '-----------------------------------------------------     
        'First insert row before history
  		dim oSheet as object
  		dim oControl as object
        oSheet = oDoc.Sheets(0)
        oSheet.Rows.insertByIndex(historybeginrow-1,1)

'Define source range and destination cell then do the 
copy..paste
        dim srcrange$
        srcrange = "A" & Ltrim(Str(row)+1) & ":Z" & 
Ltrim(Str(row+1))
        source = oSheet.getCellRangeByName(srcrange)
        cell = oSheet.getCellByPosition(0, historybeginrow-1)
        oSheet.copyRange(cell.CellAddress, source.RangeAddress)
        
        'Now copy..paste template row to row we just cut to history
        srcrange = "A" & Ltrim(Str(blanktemplaterow)) & ":Z" 
& Ltrim(Str(blanktemplaterow))
        source = oSheet.getCellRangeByName(srcrange)
        cell = oSheet.getCellByPosition(0, row)
        oSheet.copyRange(cell.CellAddress, source.RangeAddress)
        
        'Restore Broker
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnBroker,row)
    	my_cell.String = broker 	
        
        'Restore DT left (adjust if DT)
	 	my_cell = 
ThisComponent.Sheets(0).getCellByPosition(C_ColumnDTLeft,row)
    	my_cell.String = dtleft  	
        If dtstatus = C_StrDAYTRADE Then
           	Select Case dtleft
        		Case "0"
    				my_cell.String = "-" 	
        		Case "1"
    				my_cell.String = "0" 	
        		Case "2"
    				my_cell.String = "1" 	
    	    	Case "3"
    				my_cell.String = "2" 	
    		End Select
    	End If
    	
        'FIXUP our chart data source range
        ChartResetSource

'RE-Enable document recalc
       oDoc.removeActionLock()
       oDoc.unlockControllers()
   End If
End Sub

'---------------------------------------------------------------------
'   ShellSort:  Sorts array passed in Parm1
'               NO CREDITS AVAIL. SOURCED FROM:
'               https://wiki.openoffice.org/wiki/Sorting_and_searching
'---------------------------------------------------------------------
Sub ShellSort(mArray)
dim n as integer, h as integer, i as integer, j as integer, t as string,
 Ub as integer, LB as integer
Lb = lBound(mArray)
Ub = uBound(mArray)
 
' compute largest increment
n = Ub - Lb + 1
h = 1
if n > 14 then
        do while h < n
                h = 3 * h + 1
        loop
        h = h \ 3
        h = h \ 3
end if
do while h > 0
' sort by insertion in increments of h
        for i = Lb + h to Ub
                t = mArray(i)
                for j = i - h to Lb step -h
                        if strComp(mArray(j), t, 0) < 1 then exit for
                        mArray(j + h) = mArray(j)
                next j
                mArray(j + h) = t
        next i
        h = h \ 3
loop
End Sub

'-------------------------------------------------
'   Shelly:  (String to shellexecute)
'            Not my code, just my name
'-------------------------------------------------
Sub Shelly(filename as string)
    		
    '-Create the needed SystemShellExecute service object'
    DIM oShell AS OBJECT 
:oShell=createUnoService("com.sun.star.system.SystemShellExecute")
    	
    '-Get the URL from the current field'
    DIM s AS STRING      :s = convertToUrl(filename)

'-Open the URL in your browser'
    oShell.execute(s,,0)
End Sub

LearnN Stock Trading Spreadsheet for LibreOffice and Excel ^{(c)
Rob Hock 2024-2025}

SOURCE HERE:

LearnN.xlsm here! (Excel Support):

LearnN Stock Trading Spreadsheet for LibreOffice & Excel (c) Rob Hock 2024-2025
LearnN ™ is a trademark of Rob Hock - All rights reserved

LearnN Stock Trading Spreadsheet for LibreOffice and Excel (c) Rob Hock 2024-2025

SOURCE HERE:

LearnN.xlsm here! (Excel Support):

LearnN Stock Trading Spreadsheet for LibreOffice & Excel (c) Rob Hock 2024-2025 LearnN ™ is a trademark of Rob Hock - All rights reserved

LearnN Stock Trading Spreadsheet for LibreOffice and Excel ^{(c)
Rob Hock 2024-2025}

LearnN Stock Trading Spreadsheet for LibreOffice & Excel (c) Rob Hock 2024-2025
LearnN ™ is a trademark of Rob Hock - All rights reserved