📊 Торговля на Московской бирже через Excel + QUIK

27 октября 2025, 23:16
|
TaurusGOLD

🔧 Настройка связи Excel → QUIK

1. Установка необходимых компонентов

vba

' В Excel нужно добавить ссылки на библиотеки:
' 1. QUIK API (qlua.exe)
' 2. Microsoft XML
' 3. ADODB для работы с базой данных

2. Базовый шаблон торговой таблицы Excel

A	B	C	D	E	F	G
Тикер	Направление	Кол-во	Цена	Статус	ID заявки	Действие
GAZP	BUY	10	160.50	Ожидание		📥 Отправить
SBER	SELL	5	280.30	Ожидание		📥 Отправить

Код для Excel VBA

vba

' Модуль для работы с QUIK через DDE
Option Explicit

' Настройки подключения
Private Const QUIK_DDE_SERVICE = "QLUA"
Private Const QUIK_DDE_TOPIC = "QuikTrans2"
Private Const TRANS2_TABLE = "TRANS2"

' Подключение к QUIK
Public Sub ConnectToQUIK()
    On Error GoTo ErrorHandler
    
    Dim ddeChannel As Long
    ddeChannel = DDEInitiate(QUIK_DDE_SERVICE, QUIK_DTE_TOPIC)
    
    If ddeChannel > 0 Then
        ThisWorkbook.Sheets("Контроль").Range("B2"). <a name="cut"></a> Value = "Подключено"
        DDETerminate ddeChannel
    Else
        ThisWorkbook.Sheets("Контроль").Range("B2").Value = "Ошибка подключения"
    End If
    
    Exit Sub
ErrorHandler:
    MsgBox "Ошибка подключения к QUIK: " & Err.Description
End Sub

' Отправка транзакции в QUIK
Public Sub SendOrderToQUIK()
    On Error GoTo ErrorHandler
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Торговля")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastRow ' Строка 1 - заголовки
        
        ' Проверяем нажата ли кнопка отправки
        If ws.Cells(i, "G").Value = "📥 Отправить" Then
            
            Dim ticker As String
            Dim operation As String
            Dim quantity As Long
            Dim price As Double
            
            ticker = ws.Cells(i, "A").Value
            operation = ws.Cells(i, "B").Value
            quantity = ws.Cells(i, "C").Value
            price = ws.Cells(i, "D").Value
            
            ' Отправляем заявку
            Dim transactionId As String
            transactionId = SendTransaction(ticker, operation, quantity, price)
            
            ' Обновляем статус
            If transactionId <> "" Then
                ws.Cells(i, "E").Value = "Отправлено"
                ws.Cells(i, "F").Value = transactionId
                ws.Cells(i, "G").Value = "✅ Отправлено"
            Else
                ws.Cells(i, "E").Value = "Ошибка"
                ws.Cells(i, "G").Value = "❌ Ошибка"
            End If
        End If
    Next i
    
    Exit Sub
ErrorHandler:
    MsgBox "Ошибка отправки заявки: " & Err.Description
End Sub

' Функция отправки транзакции
Private Function SendTransaction(ticker As String, operation As String, _
                                quantity As Long, price As Double) As String
    
    Dim ddeChannel As Long
    ddeChannel = DDEInitiate(QUIK_DDE_SERVICE, QUIK_DDE_TOPIC)
    
    If ddeChannel = 0 Then
        SendTransaction = ""
        Exit Function
    End If
    
    ' Формируем данные для TRANS2
    Dim transactionData As String
    transactionData = "CLASSCODE=TQBR;" & _
                     "SECCODE=" & ticker & ";" & _
                     "ACTION=NEW_ORDER;" & _
                     "ACCOUNT=L01-00000F00;" & _
                     "OPERATION=" & operation & ";" & _
                     "PRICE=" & Format(price, "0.00") & ";" & _
                     "QUANTITY=" & quantity & ";" & _
                     "TYPE=L;" & _
                     "TRANS_ID=" & Format(Now, "yyyymmddhhnnss") & ";" & _
                     "BROKERREF=EXCEL_TRADE"
    
    ' Отправляем через DDE
    DDEPoke ddeChannel, TRANS2_TABLE, transactionData
    
    ' Генерируем ID транзакции
    SendTransaction = "T" & Format(Now, "yyyymmddhhnnss")
    
    DDETerminate ddeChannel
End Function

🔄 Автоматический импорт котировок из QUIK

vba

' Импорт стакана котировок
Public Sub ImportQuotesFromQUIK()
    On Error GoTo ErrorHandler
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Котировки")
    
    Dim tickers As Variant
    tickers = GetTickerList()
    
    Dim i As Long
    For i = LBound(tickers) To UBound(tickers)
        ImportTickerData tickers(i), ws, i + 1
    Next i
    
    Exit Sub
ErrorHandler:
    MsgBox "Ошибка импорта котировок: " & Err.Description
End Sub

' Получение данных по тикеру
Private Sub ImportTickerData(ticker As String, ws As Worksheet, rowNum As Long)
    Dim ddeChannel As Long
    ddeChannel = DDEInitiate(QUIK_DDE_SERVICE, "QUOTES")
    
    If ddeChannel > 0 Then
        ' Получаем основные параметры
        ws.Cells(rowNum, 1).Value = ticker
        ws.Cells(rowNum, 2).Value = DDERequest(ddeChannel, ticker & "|LAST")
        ws.Cells(rowNum, 3).Value = DDERequest(ddeChannel, ticker & "|BID")
        ws.Cells(rowNum, 4).Value = DDERequest(ddeChannel, ticker & "|ASK")
        ws.Cells(rowNum, 5).Value = DDERequest(ddeChannel, ticker & "|VOLUME")
        
        DDETerminate ddeChannel
    End If
End Sub

' Список отслеживаемых тикеров
Private Function GetTickerList() As Variant
    GetTickerList = Array("GAZP", "SBER", "LKOH", "GMKN", "ROSN", "NVTK", "TATN", "MTSS", "AFKS", "VTBR")
End Function

📈 Расширенная торговая панель

vba

' Автоматический торговый робот
Public Sub AutoTradingRobot()
    Dim wsTrade As Worksheet
    Set wsTrade = ThisWorkbook.Sheets("Автоторговля")
    
    Dim wsQuotes As Worksheet
    Set wsQuotes = ThisWorkbook.Sheets("Котировки")
    
    ' Получаем текущие цены
    UpdateAllQuotes
    
    ' Анализируем и торгуем
    Dim lastRow As Long
    lastRow = wsTrade.Cells(wsTrade.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastRow
        ProcessTradingSignal wsTrade, i
    Next i
End Sub

' Обработка торговых сигналов
Private Sub ProcessTradingSignal(ws As Worksheet, rowNum As Long)
    Dim ticker As String
    Dim strategy As String
    Dim signal As String
    
    ticker = ws.Cells(rowNum, 1).Value
    strategy = ws.Cells(rowNum, 2).Value
    signal = AnalyzeSignal(ticker, strategy)
    
    If signal <> "HOLD" Then
        ' Выполняем торговую операцию
        ExecuteAutoTrade ticker, signal, ws.Cells(rowNum, 3).Value
        ws.Cells(rowNum, 4).Value = signal
        ws.Cells(rowNum, 5).Value = Now
    End If
End Sub

' Анализ сигналов
Private Function AnalyzeSignal(ticker As String, strategy As String) As String
    Dim currentPrice As Double
    Dim bidPrice As Double
    Dim askPrice As Double
    
    ' Получаем текущие цены
    currentPrice = GetCurrentPrice(ticker)
    bidPrice = GetBidPrice(ticker)
    askPrice = GetAskPrice(ticker)
    
    Select Case strategy
        Case "MEAN_REVERSION"
            AnalyzeSignal = MeanReversionSignal(ticker, currentPrice)
        Case "BREAKOUT"
            AnalyzeSignal = BreakoutSignal(ticker, currentPrice)
        Case "ARBITRAGE"
            AnalyzeSignal = ArbitrageSignal(ticker, bidPrice, askPrice)
        Case Else
            AnalyzeSignal = "HOLD"
    End Select
End Function

🗃 Мониторинг портфеля и заявок

vba

' Импорт состояния портфеля
Public Sub ImportPortfolio()
    Dim wsPortfolio As Worksheet
    Set wsPortfolio = ThisWorkbook.Sheets("Портфель")
    
    wsPortfolio.Cells.ClearContents
    
    ' Заголовки
    wsPortfolio.Range("A1:E1").Value = Array("Тикер", "Количество", "Средняя", "Текущая", "P&L")
    
    ' Импорт позиций через DDE
    Dim ddeChannel As Long
    ddeChannel = DDEInitiate(QUIK_DDE_SERVICE, "DEPO")
    
    If ddeChannel > 0 Then
        Dim i As Long: i = 2
        Dim securities As Variant
        securities = GetTickerList()
        
        Dim ticker As Variant
        For Each ticker In securities
            ImportPosition ddeChannel, ticker, wsPortfolio, i
            i = i + 1
        Next ticker
        
        DDETerminate ddeChannel
    End If
End Sub

' Импорт позиции по тикеру
Private Sub ImportPosition(ddeChannel As Long, ticker As String, ws As Worksheet, rowNum As Long)
    On Error Resume Next
    
    ws.Cells(rowNum, 1).Value = ticker
    ws.Cells(rowNum, 2).Value = DDERequest(ddeChannel, ticker & "|TOTAL_NET")
    ws.Cells(rowNum, 3).Value = DDERequest(ddeChannel, ticker & "|AVG_PRICE")
    ws.Cells(rowNum, 4).Value = DDERequest(ddeChannel, ticker & "|LAST")
    
    ' Расчет P&L
    If IsNumeric(ws.Cells(rowNum, 2).Value) And IsNumeric(ws.Cells(rowNum, 4).Value) Then
        Dim pnl As Double
        pnl = (ws.Cells(rowNum, 4).Value - ws.Cells(rowNum, 3).Value) * ws.Cells(rowNum, 2).Value
        ws.Cells(rowNum, 5).Value = pnl
    End If
End Sub

🎯 Готовые торговые стратегии

vba

' Стратегия статистического арбитража
Public Sub StatisticalArbitrage()
    Dim pair1 As String: pair1 = "GAZP"
    Dim pair2 As String: pair2 = "LKOH"
    
    Dim price1 As Double: price1 = GetCurrentPrice(pair1)
    Dim price2 As Double: price2 = GetCurrentPrice(pair2)
    
    Dim spread As Double: spread = price1 / price2
    Dim meanSpread As Double: meanSpread = 0.85 ' историческое среднее
    Dim stdDev As Double: stdDev = 0.05 ' стандартное отклонение
    
    If spread > meanSpread + 2 * stdDev Then
        ' GAZP перекуплен относительно LKOH
        ExecutePairTrade pair1, "SELL", pair2, "BUY", 10
    ElseIf spread < meanSpread - 2 * stdDev Then
        ' GAZP перепродан относительно LKOH  
        ExecutePairTrade pair1, "BUY", pair2, "SELL", 10
    End If
End Sub

' Исполнение парной сделки
Private Sub ExecutePairTrade(ticker1 As String, operation1 As String, _
                           ticker2 As String, operation2 As String, quantity As Long)
    
    SendTransaction ticker1, operation1, quantity, 0
    SendTransaction ticker2, operation2, quantity, 0
End Sub

⚙ Настройка автоматического обновления

vba

' Запуск автоматического обновления
Public Sub StartAutoUpdate()
    Application.OnTime Now + TimeValue("00:00:05"), "UpdateTradingData"
End Sub

' Основная процедура обновления
Public Sub UpdateTradingData()
    On Error GoTo ErrorHandler
    
    ' Обновляем котировки
    ImportQuotesFromQUIK
    
    ' Обновляем портфель
    ImportPortfolio
    
    ' Запускаем автоторговлю если включена
    If ThisWorkbook.Sheets("Настройки").Range("B2").Value = "ДА" Then
        AutoTradingRobot
    End If
    
    ' Планируем следующее обновление
    Application.OnTime Now + TimeValue("00:00:10"), "UpdateTradingData"
    
    Exit Sub
ErrorHandler:
    MsgBox "Ошибка обновления: " & Err.Description
    Application.OnTime Now + TimeValue("00:00:30"), "UpdateTradingData"
End Sub

' Остановка автоматического обновления
Public Sub StopAutoUpdate()
    On Error Resume Next
    Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _
                      Procedure:="UpdateTradingData", Schedule:=False
End Sub

🚀 Инструкция по запуску: