Adnan Salih [email protected]

Excel strateji geliştirme ve optimizasyon

Son yazımda volatilite hedefi ile BİST30 stratejisini ele almıştım.
Neden volatiliteyi önemsiyorum?
Volatilite portföyünüzü taşırken ödediğiniz fiyattır. En az ödeyip en fazla kazanmanın yollarını aramak gerekir.
Geliştirdiğim modelde kullanıcı volatilite hedefini kendi belirleyip dosyayı kullanarak hisse ağırlıklarını hesaplayabilir.
Stratejide hemen hemen her şey manuel olarak yapılıyordu.
Bugün önceki yazıda kullandığım Excel dosyasını geliştirip kod ekleyerek optimize etmek istiyorum.
Portföye dair optimizasyon ve dağılım yapmak istiyorsanız mutlaka kod kullanmanız gerekir.
Excel için bu kodun adı VBA’dır. (İngilizce açılımı: Visual Basic for Applications)
VBA öğrenmek için internet kaynakları veya kitaplardan faydalanabilirsiniz. Excel çevrimiçi yardım da iyi bir kaynaktır.
Excel’de optimizasyon yapmak için Solver (Çözücü) eklentisi kullanılır. Bu eklentiyi kullanabilmek için Dosya-Seçenekler-Eklentiler adımlarını takip edip git tuşuna basarak gelen ekranda çözücü / solver etkinleştirmeniz gerekir:


Bu adımları yaptıktan sonra Excel VBA ortamında refarans olarak tanıtmak gerekir. Excel VBA ortamı Alt+F11 tuş kombinasyonu ile açılır.
Tools menü adımında References seçip:

Gelen pencerede Solver kutucuğunu işaretlemeniz gerekir:

Bunları yaptıktan sonra ekranın sol tarafı şöyle görünmeli:

Tüm hazırlıklar tamam.
Dosyada bazı değişiklikler yapmak gerekti. Bunun için korelasyon hesaplanan bölgeyi corrMat olarak adlandırdım. Kayan pencerede hesaplayabilmek için başla ve bitir isimli iki alan yarattım. Ağırlıkları geçici olarak hesaplayacağım wVec isimli bir satır vektörü oluşturdum. volHedef ve volTarget isimli iki hücre daha ekledim.
volHedef hücresine erişmek istediğiniz volatiliteyi eklemeniz gerekiyor.
Her ayın portföy getirisini hesaplamak için de görünürde karmaşık bir formül kullandım:
=TOPLA.ÇARPIM((İNDİS(px!$D$1:$D$611;D3):İNDİS(px!$AG$1:$AG$611;D3)/İNDİS(px!$D$1:$D$611;D2):İNDİS(px!$AG$1:$AG$611;D2)-1);L2:AO2)
İNDİS hücre aralıklarını getiriyor.
Hesaplamaları gerçekleştirmek için ise aşağıdaki kısa kodu yazdım:
Sub SolverMacro()
vol = Range("volHedef") * 1
Range("basla").Value = 0
Range("bitir").Value = 0
SolverReset
Call SolverOk(Range("volTarget"), 3, vol, Range("wVec"))
For i = 1 To 27
Range("basla").Value = Cells(i + 1, 5).Value
Range("bitir").Value = Cells(i + 1, 6).Value
Call SolverSolve(True)
SolverSolve userFinish:=True
Range("wVec").Select
Selection.Copy
Cells(i + 1, 12).Select
Selection.PasteSpecial Paste:=xlValues
Next i
End Sub
Hesaplamalar 27 döem için yapılıyor, döngü o yüzden 1:27 arasında. Döngüden önce solver eklentisine değişkenleri aktarmak gerekiyor. Bunu da
Call SolverOk(Range("volTarget"), 3, vol, Range("wVec"))
Satırı ile gerçekleştiriyoruz. Buradaki 3 rakamı önemli.
Solver volTarget hücresini vol değerine eşitlemek için wVec satırındaki ağırlıkları değiştiriyor. Solver aynı zamanda min ve max için de çözüm yapabiliyor. O zaman 3 rakamı yerine 1 veya 2 gelmesi gerekir.
Ben %30 volatilite hedefli bir portföy oluşturduğumda getiri grafiği aşağıdaki gibi gerçekleşti:

İşlemleri gerçekleştirdikten sonra özet sayfasına gidip tabloları yeniledikten sonra detaylı getirileri de inceleyebilirsiniz:

27 simülasyon bilgisayar konfigürasyonuna bağlı olarak 8-10 dk. sürebilir.
Dosyayı incelemek için:
https://goo.gl/o17kFU 

Sayfada yer alan bilgiler tavsiye niteliği taşımayıp yatırım danışmanlığı kapsamında değildir. Yatırımcı profilinize uymayabilir.