Formulas in datagridview cells redux

[dinotom]

I apologize to the forum if parts of this were posted earlier but I cannot get the problem resolved so I have put a very detailed post here. Any help would be most appreciated.

To summarize, I have a datagridview that is fed its initial data via a text file that is read in to populate the datagridview of its initial cell values. (They are symbols for a datafeed) . The unbound columns that I am adding need formulas based on the symbols as the formula cell for each row can be different. I have tried any method I can think of to iterate through to set the formula. I have even tried to set a simple formula ie, one cell value - another cell value and still get blank cells.

At this point I am wondering if I am even setting the datagridview up properly to even allow this. The grid looks fine when running and the data from the datafeed comes in fine and updates.

The two code sets below are what get the data table and the form load event.

Data table function, note all the comments

 Function GetTable() As DataTable
        ' Create new DataTable instance.
        Dim table As New DataTable
        ' Create four typed columns in the DataTable.
        table.Columns.Add("ADR Symbol", GetType(String))
        table.Columns.Add("ADR Price", GetType(Double))
        table.Columns.Add("ORD Symbol", GetType(String))
        table.Columns.Add("ORD Price", GetType(Double))
        table.Columns.Add("Ratio", GetType(Double))
        table.Columns.Add("Currency", GetType(String))
        table.Columns.Add("Currency Price", GetType(Double))
        'trying a new way to set the column to add an expression
        Dim cDiff As DataColumn = New DataColumn
        With cDiff
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "Difference"


            'setting the expression here results in all cells having same formula
            ' but the gridview doesnt know what the currencies are here yet to set the formula
            'cell by cell yet
            '.Expression = "[ADR Price]-([ORD Price] * [Ratio] * [Currency Price])"
        End With
        table.Columns.Add(cDiff)
        table.Columns.Add("GoNoGo", GetType(String))


        'start to loop thru all the symbols in the symbols file
        ' first read them into an array
        'the line below is for the commodity symbols file
        Dim strFile As String = strDrive & "ADR-ORD\ADR-ORD Comparator\ADR-ORD Comparator \SymbolsinBBRGFormat.txt"
        If Not File.Exists(strFile) Then
            strFile = Environment.CurrentDirectory & "\SymbolsinBBRGFormat.txt"
            Debug.Print(strFile)
        End If


        Dim strText As String = File.ReadAllText(strFile)
        Dim aryText() As String = strText.Split(";")
        strText = vbNullString
        Dim x As Integer
        Dim symADR As String = "", symORD As String = 0, strCurncy As String = ""
        Dim dblADRPx As Double = 0, dblORDPx As Double = 0, dblCurncyPx As Double = 0
        Dim ratio As Double = 1, diff As Double = 0, goNogo As String = ""


        For x = 0 To UBound(aryText) - 1
            Dim newADRrow As DataRow = table.NewRow()
            newADRrow("ADR Symbol") = Trim(Split(aryText(x), ",")(0))
            newADRrow("ADR Price") = 1 'add bbrg formula here?
            newADRrow("ORD Symbol") = Trim(Split(aryText(x), ",")(1))
            newADRrow("ORD Price") = 1 ' add bbrg formula here?
            newADRrow("Ratio") = Split(aryText(x), ",")(2)
            newADRrow("Currency") = Trim(Split(aryText(x), ",")(3))
            newADRrow("Currency Price") = 1


            'clearly this won't work as the intellisense didnt capitalize the .value
            'If newADRrow("Currency").ToString() = "JPY Curncy" Then
            '    newADRrow("Difference") = newADRrow("ADR Symbol").value - newADRrow("ORD Symbol").value
            'End If
            newADRrow("Difference") = 1
            newADRrow("GoNoGo") = "Go"
            table.Rows.Add(newADRrow)
        Next x


        'For Each row As DataRow In table.Rows
        '    Debug.Print(row("Currency").ToString())
        '    If row("Currency").ToString() = "EUR Curncy" Or row("Currency").ToString() = "SEK Curncy" _
        '     Or row("Currency").ToString() = "AUD Curncy" Or row("Currency").ToString() = "CAD Curncy" Then
        '        row("Difference"). = "[ADR Price]-([ORD Price] * [Currency Price])"
        '    Else
        '        row("Difference") = "[ADR Price]-([ORD Price] / [Currency Price])"
        '    End If
        'Next
        Return table
    End Function

Here is the form load event, note all the comments

 Private Sub frmADRORD_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        'wire the delegate function for incoming updates
        AddHandler m_helper.DataUpdate, AddressOf UpdateGridInvoker
        'bind the visual grid with the binding source
        Me.datagridADRORD.DataSource = dsGridView


        'get table data from helper class
        m_dt = m_helper.GetTable()


        'bind the binding source with datatable
        Me.datagridADRORD.DataSource = m_dt


        ' this is my unbound test column to try to get this formula thing working
        'I cant get any value to show in it
        'With Me.datagridADRORD
        '    .Columns.Add("Historical Diff", "Historical Diff")
        '    .Columns("Historical Diff").Width = 70
        '    '.columns"Historical Diff").gettype(
        'End With
        'For Each row As DataGridViewRow In Me.datagridADRORD.Rows
        '    row.Cells("Historical Diff").Value = Double.Parse(row.Cells("ADR Price").Value.ToString())


        'Next
        'here is another of the myriad ways I have tried to get this to work


        'For Each row As DataGridViewRow In Me.datagridADRORD.Rows
        '    row.Cells("Difference").ReadOnly = False
        '    If (row.Cells("Currency").Value = "JPY Curncy" Or row.Cells("Currency").Value = "CHF Curncy") Then
        '        row.Cells("Difference").Value = row.Cells("ADR Price").Value - ((row.Cells("ORD Price").Value * row.Cells("Ratio").Value) / row.Cells("Currency Price").Value)
        '    Else
        '        row.Cells("Difference").Value = row.Cells("ADR Price").Value - ((row.Cells("ORD Price").Value * row.Cells("Ratio").Value) * row.Cells("Currency Price").Value)
        '    End If




        'after data loaded, auto resize columns and format
        'Me.datagridADRORD.AutoResizeColumn(DataGridViewAutoSizeColumnMode.AllCellsExceptHeader)
        ' Set the cell styles
        SetDefaultCellStyles()
    End Sub