Happy Equations

Excel是功能很強的計算工具, 對一般投資人而言, 通常資料的來源是比較大的問題. 有許多人是每天去下載交易所的資料存到Excel檔或是文字檔, 也有些資訊廠商有提供歷史資料下載. 在此我示範一下從 Yahoo!Finance 下載資料. 台灣的Yahoo!奇摩已經不提供歷史價格下載了, 美國的Yahoo仍有全球主要股市的歷史價格下載功能, 而且很方便.

1. 進入Yahoo!Finance, http://finance.yahoo.com 
不懂英文無所謂, 我們只需要幾個功能. 首先在標籤下方有 GET QUOTES 這個按鈕, 旁邊有一個框框可以輸入股票代碼. 由於Yahoo!Finance提供的是全球的股票, 所以要找台灣股票要在代碼後面加 .TW. 要找台積電則輸入 2330.TW 按 GET QUOTES

2. 2330.TW的資料出現之後, 在左方功能中有 Historical Prices, 按這個連結會顯示 2330.TW的歷史資料.

3. 預設的資料長度是5年, 每頁會顯示大約66天的資料. 將畫面移到最下方, 按一下 Download To Spreadsheet

4. 選擇 開啟檔案, Excel會自動開啟顯示2330.TW的五年歷史資料. 我們需要的是G欄的 Adj. Close, 這表示有調整過的價格.

5. 在 H欄輸入報酬率的公式. 報酬率的算法是 (當日-昨日)/昨日. 注意! 日期順序為新的在上. 例如H2的公式是 =(G2-G3)/G3

6. 將公式複製到一年份, 大約是262筆資料

7. 打開一份新的Excel檔案, 將剛剛計算的報酬率用 選擇性貼上/貼上值 貼到新的工作表

8. 把原來的歷史價格Excel關閉. 重複一次從Yahoo!Fiance下載資料, 將 2303.TW 的報酬率計算出來, 跟2330.TW貼在一起.

9. 利用Excel函數 CORREL 計算報酬率的相關係數.

注意: 所計算出來的報酬率略有偏差, 依不同狀況仍需做適當調整. 例如某一檔股票暫停交易期間, 應將報酬率資料刪除再行計算相關性.

今天無意間發現我的 愛上大溪地 網站已經在 Google 的關鍵字 大溪地 中排名第二位,
Page Rank 目前是 3.

不過更令我驚訝的是即使是關鍵字 大溪, 網站也排名第六....

I attended the Financial Summit Workshopon Sep 21-22 at National Taiwan University in Taipei. Many speakers provided great tutorial and speeches during the sessions. The most important guest is Dr. Edward Altman from Stern School of Business New York University.

Dr. Altman is the developer of the famous Z-Score method for evaluating credit risk of public-traded firms. Although he developed the very first model in 1967, it is still very useful in today's financial environment. Dr. Altman mentioned about the difficulty of developing such a complex model during ancient punch card age of computers. I was surprised by the accuracy of the model.

Dr. Altman has great humor and talked many jokes in his sessions. He also like default and bankrupcy of major companies. I was impressed and learned  a lot during this workshop.

We finished first release of RightPairs for pair trading last week. We are very happy because it is the most powerful generic calculation agent we ever developed.

First of all, it can run on multiple PCs and multi-threads. We tested the calculation on three 4-CPU servers, each server run calculation agents in 12 threads, without any problem. It takes full advantage of multi-processor and multi-thread. Currently it do only correlations, but it is quite easy to add more calculation features.

Our software is at http://www.rightpairs.com

I have not studied deeply about partial matching. Someone asked about using regular expression for partial matching ACGT-like pattern and strings. I wrote a simple program to test my idea about partial matching using regular expression.

The first effort is on brute force partial matching. I prepared all the possible patterns and match them to the target string. It worked fine if you can only accept 1 tolerrance (1 doesn't match) since the number of patterns to match is equal to the length of original pattern. But if you want more than 1 tolerrance, the number of patterns goes up to C(pattern length, tolerrance), it might drive you crazy if you have a pattern more than 100.

I tried to use another technique "Check Appearance". I don't know whether it has an official or scientific name. First I get a substring which length is the same as pattern from target string. Then I compare the frequency of A, C, G, T in the pattern and the new string. If their differerence are below the tolerrance, than I can do the RegEx matching. I found it is useful especially the pattern is long and tolerrance is low. I should try some other methods if I have time.

I attach my all source code here. Please understand the code is developed for quick testing, not a full functional release. There are many bugs and accuracy of the result must be checked. Please let me know if you find any bugs.

Download link has been moved to JumboGuide.

There are many free data on the net, but nobody collect and use them. I am starting a project to collect various data on the net. Using regular expression provided by .NET, I think it is quite simple to collect web data. I have put related works on http://www.derivativepower.com and http://www.jumboguide.com

After waiting for 2 months for exam result, I finally got a notification from GARP that I passed 2005 FRM Exam.

I thought I might not pass it because I didn't know how to solve most of questions in the exam. I am lucky enough that I got good scores in many section. The exam result shows only in Quartile.

Credit Risk Measure and Management: 1st Quartile
Market Risk Measure and Management: 2nd Quartile
Operational and Integrated Risk Management, Legal, Accounting, and Ethics: 2nd Quartile
Quantitative Analysis: 1st Quartile
Risk Management in Investment Management: 1st Quartile

FpML (Financial product Markup Language) is a good choice to store and to process financial derivatives. Current specification 4.2 Working Draft has defined the following produts:

• Interest Rate Swap (plain vanilla IRS, quanto swap)
• Interest Rate Option (caps/floors/collars)
• Credit Default Swap
• Equity Forward
• Equity Option (European, American, Bermuda, Exotic)
• Equity Swap
• Asset Swap
• Forward Rate Agreement (FRA)
• FX Average Rate Option
• FX Barrier Option
• FX Digital Option
• FX Simple Option
• FX Swap
• Swaption

For more information, you can go to FpML Website.

I spotted the Google Map APIs for a while. In my opinion, as a web developer in Taiwan, I might choose Google Map as my tool. I don't mean Google Map is better than ArcWeb Services. In fact, Google Map offer just basic mapping APIs and can't compare with ESRI ArcWeb. But ESRI ArcWeb Services offers little content outside US. We can't get detailed sattlelite images of Taiwan from ArcWeb. Google Map and Google Earth really have very detailed images of urban areas in Taiwan.

But, Google Map has no road map in Taiwan, although ArcWeb has only outdated large scale road map. The best choice is to have your own ArcGIS Server and purchase your own data, it might cost tens thousands US dollars. If you are looking for free services, there are always trade-offs.

For information about ArcWeb Services, http://www.esri.com/software/arcwebservices/index.html

For information about Google Map API: http://www.google.com/apis/maps/

Taiwan is a beautiful island with many beautiful scenes. It has more than 100 peaks taller than 3000 meters (9000 feet). In 2003, Taiwanese government picked 100 mountains which were accessible in 1 day from urban area. Most of them have roads directly connected to the trails and closed to major cities. The elevations are from 100 meters to more than 2000 meters.

Currently I can't find any information about these trails in English, so I will provide information at my travel website at http://www.jumboguide.com/guide/tt.aspx?forumid=45&lang=English

Although it is Saturday, my wife and me woke up at 5:30am. We were going to find our first geocache. We picked the nearest cache, only 1km away my home, at the top of Hemei Mountain by the Green Lake (BiTan). We arrived the platform on the top of trail. It took us 20 minutes to search the cache. Finally we found it. There are tens have logged it. We took an elephant key ring and put some old Taiwanese stamps in it. We are very happy for success at our first hunt.

Note: For visitors of your site, this entry is only displayed for users with the preselected language English/English (en)

I studied FRM materials and got an indea last night. It is hard to define the operational risk of financial institutes, in fact, any institutes. But a multi-agent system might be a good choice to simulate operational risk. Blow is a rough idea:

To simulate operational risk in Department A, which has
1 department head, 10 traders, 2 risk managers, 5 back office

defined communication lines:
deapertment head to everyone: 17
traders cross: 10x9=90
traders to risk managers: 10x2=20
traders to back office have no full route: 15
risk managers to back office: 2x5=10

defined probability of error of each person and each lines
defined potential loss of each person when he/she failed to perform unintentionally

defined probability of intentional error of each person
defined potential loss of each person when he/she failed to perform intentionally

We can now start to simulate. This model is too simple for any existing environment. We can have the PCs, telecomm lines, and any equipments into the model. Also external events and entities. If we got 500 persons and 10000 entities, we still can do it use MAS.

Let it be my next project.

Note: For visitors of your site, this entry is only displayed for users with the preselected language English/English (en)

I was asked about VaR of a 20 years fixed-income or fund. VaR is an estimate about potential profit or loss in a short term, says 1, 5, or 10 days. How about VaR for a 20 years maturity government bond? Or for someone want to evaluate his long-term investment?

We know VaR is getting larger as the term longer:

n days VaR = 1 day VaR * sqrt(n)

if 1 day VaR is v
5 days VaR is v * sqrt(5) = 2.236v
10 days VaR is v * sqrt(10) = 3.162v

if you evaluate longer term
365 days VaR is 19.105v
3650 days VaR is 60.415v
7300 days VaR is 85.440v

Wow, the potential profit or loss might be very high.

By definition is true, of course, but we know it is not in practice. Why?

Because long term volatility is hard to estimate and the rule of square n is not a good way to calculate even short term VaR.

I clawed the webs and found only one paper about long term VaR model.
See Long-Term Value at Risk. By. Kevin Dowd, David Blake, and Andrew Cairns

Note: For visitors of your site, this entry is only displayed for users with the preselected language English/English (en)

Date 12/11/2004

After passing the custom, a beautiful Polyneasian girl present wreaths for us. Because our cruise will start embarking next day, we take a bus to the hotel IntercContinental BeachComber Tahiti. 30 odd people are probably all westerners on the bus, only another pair of Oriental's face. We knew later that they are Chinese lived in San Francisco. All the travellers on the cruise, as we knew, only we come from Asia. All others are very surprised we come from so far a place. In fact, it is only a little farer compare with European visitors. The Europeans all take plane from Paris to Los Angeles to transfer to Tahiti. Travel by cruise is familiar by westerners but not for orientals, that's why there are only few orientals onboard.

Arriving the hotel ten minutes later, again we waited for a long time too of check-in, It seems the people here are not in a hurry.  A beautiful girl at the other side of counter answered every questions slowing. A group of travellers carried the luggage and wait behind of us. Wait until our check-in When the check-in is finished, all of us are nailed black and blue by the mosquito.

Because we are only one night in transit here, so has not lived in the "over the water bungalow". Our room is rich of south asian style, coconut trees outside the balcony hide most of our view of sea. TVs are mostly French programs, two or three English movie channels and ESPN, but we do not want to watch TV. We put down our baggages and go to the first floor to see tonight's performance.

We sat on a good place in the restaurant and paid the first bill in Tahiti. The price on the menu is franc CFP of Pacific Ocean of local currency, we are not still very familiar with the exchange rate, two cups of fruit juice, unexpectedly it is nearly 17 euro. Fortunately the meal will be served free on the cruise from tomorrow. Otherwise it can really called very good weight-reducing journey.

The show is traditional Polyneasian dance. If you have ever been to Guam or Hawaii must see the similar performance. Strong rhythm and wild movementare the characteristics of the Oceania dance. The dancers invited audienced to show on the stage and perform together. We have a lot of fun.

The poolside after the performance was very quiet, and the scene of hotel is very beautiful too. When I was planning the honeymoon, my wife said it was very regrettable that we are in Tahiti but not lived in the on the water bungalow. I think it is not a issue after this night on the ground.