http://bfexplorer.net/Articles/Content/529

PoolPriceToSpreadsheet.fsx

// Bfexplorer cannot be held responsible for any losses or damages incurred during the use of this betfair bot.
// It is up to you to determine the level of risk you wish to trade under. 
// Do not gamble with money you cannot afford to lose.

module BfexplorerBot

#I @"C:\Program Files (x86)\BeloSoft\Bfexplorer\"

#r "BeloSoft.Data.dll"
#r "BeloSoft.DataAnalysis.dll"
#r "BeloSoft.Betfair.API.dll"
#r "BeloSoft.Bfexplorer.Domain.dll"
#r "BeloSoft.Bfexplorer.Trading.dll"
#r "BeloSoft.Bfexplorer.Service.Core.dll"

#r "DevExpress.Data.v15.1.dll"
#r "DevExpress.Office.v15.1.Core.dll"
#r "DevExpress.Spreadsheet.v15.1.Core.dll"

open System

open DevExpress.Spreadsheet
open DevExpress.XtraSpreadsheet

open BeloSoft.Data
open BeloSoft.DataAnalysis.Models
open BeloSoft.Bfexplorer.Domain
open BeloSoft.Bfexplorer.Trading

/// <summary>
/// SelectionData
/// </summary>
type SelectionData(selection : Selection) =

    let totalMatchedHistory = HistoryValue(selection.TotalMatched)    
    let poolPrice = HistoryValue(0.0)

    let mutable trend = PriceTrend.Stable

    member _this.Name
        with get() = selection.Name

    member _this.Price
        with get() = selection.LastPriceTraded

    member _this.PoolPrice
        with get() = poolPrice.Value

    member _this.Trend
        with get() = trend

    member _this.RecalculatePoolPrice(marketTotalMatched : float) =
        let value =
            if totalMatchedHistory.SetValue(selection.TotalMatched)
            then
                let totalMatched = totalMatchedHistory.Value - totalMatchedHistory.PreviousValue

                toProbabilityPrice ((totalMatched / marketTotalMatched) * 100.0)
            else
                0.0

        if poolPrice.SetValue(value)
        then
            trend <- 
                if value = 0.0
                then
                    PriceTrend.Stable
                else
                    if poolPrice.PreviousValue > value
                    then
                        PriceTrend.Fall
                    else
                        PriceTrend.Rise

/// <summary>
/// TriggerStatus
/// </summary>
type TriggerStatus =
    | Initialize
    | UpdateData

/// <summary>
/// PoolPriceToSpreadsheet
/// </summary>
type PoolPriceToSpreadsheet(market : Market, _selection : Selection, _botName : string, botTriggerParameters : BotTriggerParameters, myBfexplorer : IMyBfexplorer) =

    let updateInterval = TimeSpan.FromSeconds(defaultArg (botTriggerParameters.GetParameter<float>("UpdateInterval")) 5.0)

    let mutable triggerStatus = TriggerStatus.Initialize
    let mutable timeToUpdate = DateTime.MinValue

    let totalMatchedHistory = HistoryValue(market.TotalMatched)
    let mutable selectionDatas = nil<SelectionData list>

    let mutable bfexplorerSpreadsheet = nil<ISpreadsheetControl>
    let mutable worksheet = nil<Worksheet>

    let setValue value (row, column) =
        worksheet.[row, column].SetValue(value)

    let initialize() =
        selectionDatas <-
            market.Selections
            |> Seq.filter isActiveSelection
            |> Seq.map SelectionData
            |> Seq.toList
        
        let worksheetName = market.Id
        let workbook = bfexplorerSpreadsheet.Document

        worksheet <-
            if workbook.Worksheets.Contains(worksheetName)
            then
                workbook.Worksheets.[worksheetName]
            else
                workbook.Worksheets.Add(worksheetName)

        let minPoint = worksheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "-1", ConditionalFormattingValueOperator.GreaterOrEqual)
        let midPoint = worksheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual)
        let maxPoint = worksheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "1", ConditionalFormattingValueOperator.GreaterOrEqual)

        let addConditionalFormattings (row : int, column : char) =
            let cfRule = worksheet.ConditionalFormattings.AddIconSetConditionalFormatting(worksheet.Range.[sprintf "$%c$3:$%c$%d" column column row], IconSetType.Arrows3, [| minPoint; midPoint; maxPoint |])
            cfRule.IsCustom <- true
            cfRule.SetCustomIcon(1, ConditionalFormattingCustomIcon(IconSet = IconSetType.TrafficLights13, IconIndex = 1))
            cfRule.ShowValue <- false

        try
            bfexplorerSpreadsheet.BeginUpdate()

            (0, 0) |> setValue market.MarketFullName
            (1, 1) |> setValue "Price"
            (1, 2) |> setValue "Pool Price"

            let totalMatched = totalMatchedHistory.Value
            let mutable row = 2

            selectionDatas
            |> List.iter (fun selectionData ->

                    selectionData.RecalculatePoolPrice(totalMatched)

                    (row, 0) |> setValue selectionData.Name
                    (row, 1) |> setValue selectionData.Price
                    (row, 2) |> setValue selectionData.PoolPrice
                    (row, 3) |> setValue 0

                    row <- row + 1
                )

            addConditionalFormattings (row, 'D')
        finally
            bfexplorerSpreadsheet.EndUpdate()

    let update() =
        if totalMatchedHistory.SetValue(market.TotalMatched)
        then
            try
                bfexplorerSpreadsheet.BeginUpdate()

                let totalMatched = totalMatchedHistory.Value - totalMatchedHistory.PreviousValue
                let mutable row = 2

                selectionDatas
                |> List.iter (fun selectionData ->

                        selectionData.RecalculatePoolPrice(totalMatched)

                        (row, 1) |> setValue selectionData.Price
                        (row, 2) |> setValue selectionData.PoolPrice
                        (row, 3) |> setValue (int selectionData.Trend)
                        
                        row <- row + 1
                    )
            finally
                bfexplorerSpreadsheet.EndUpdate()
        
    interface IBotTrigger with

        /// <summary>
        /// Execute
        /// </summary>
        member _this.Execute() =
            match triggerStatus with
            | TriggerStatus.Initialize ->

                bfexplorerSpreadsheet <- myBfexplorer.BfexplorerService.Bfexplorer.BfexplorerSpreadsheet

                if isNullObj bfexplorerSpreadsheet
                then
                    TriggerResult.EndExecutionWithMessage "Open bfexplorer spreadsheet application!"
                else
                    initialize()
                    triggerStatus <- TriggerStatus.UpdateData
                    TriggerResult.WaitingForOperation
                                
            | TriggerStatus.UpdateData ->

                let time = DateTime.Now

                if time >= timeToUpdate
                then
                    update()
                    timeToUpdate <- time.Add(updateInterval)
                
                TriggerResult.WaitingForOperation

        /// <summary>
        /// EndExecution
        /// </summary>
        member _this.EndExecution() =
            ()