[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[amibroker] Re: Portfolio Backtesting spreadsheet( Attn. HB)



PureBytes Links

Trading Reference Links

Hello HB,

I have just found and downloaded your Portfolio Backtesting 
Spreadsheet. (Great Idea, by the way)

However I am having a small problem I'm hoping you can help with:

After pasting my data into the "Data" Worksheet, and clicking on 
the "Apply Backtest" button, I get an error message:

Runtime Error '13':
Type mismatch

Then entering debug, it highlights the following line in the VB Code:

sharesowned = sharesowned - Worksheets("Trades").Range(sharesaddress)


(located in the "Do" loop in the 'ElseIf action = "Sell" Then' 
segment of the 'Private Sub CommandButton1_Click()' page.


I can't get beyond this, not knowing enough about VB to even begin to 
troubleshoot it myself.

But I am so interested in employing this tool, that I just had to ask 
if you could perhaps find enough time to offer some suggestion or aid 
in solving this issue.

Best Regards,

Nick Molchanoff




--- In amibroker@xxxxxxxxxxxxxxx, "HB" <hmab@xxxx> wrote:
> Hello
> 
> I have attached a zipped spreadsheet which is a multi-security 
portfolio backtesting spreadsheet.  Given the results of an AB scan 
(ticker, date, buy/sell, price), it will scan through the results and 
record trades based on your desired position size and initial total  
capital. It will then show you the trades, ending capital, profit, 
etc.
> 
> Note: I have only started learning VBA in Excel. When you 
experienced programmers look at the VBA code, you will realized that 
there are probably much better ways to do what I'm trying to do.  Any 
suggestions are more than welcome.
> 
> To use it:
> - run a scan in AB
> - sort by ascending date
> - copy the results into the worksheet labelled "Data" starting from 
cell A1
> - specify your initial capital and max postion size (fixed $ 
amount, for now) in the worksheet labelled "Results"
> - click the Apply Backtest button on the "Data" worksheet
> - once it's done, you can see the results in the "Results" 
worksheet.  you must enter the current price for open positions
> 
> The spreadsheet simply runs through the chronological list in Data 
and records a buy/sell based on the price, desired position size, max 
number of shares, and the current balance of your account. If you do 
not have enough funds, it 
> obviously does not record a trade.
> 
> Assumptions:
> 
> - It currently only handles going long, no short selling.
> 
> - You DO want to accumulate a stock you already own.  If there is a 
buy signal for a ticker which you already own, the macro currently 
accepts that buy signal (i.e. accumulates more shares). However, this 
is relatively easy to change so that it will not accumulate shares of 
a ticker you already own. Which method is more realistic in your (and 
anyone else's) view ? I could probably add an option where you 
specify whether or not you want to accumulate shares of a currently 
owned ticker.
> 
> - If a sell for a certain ticker occurs before any buy, I simply 
ignore the trade since you don't own any shares yet.
> 
> - If there are multiple buys on the same day, the spreadsheet 
process them in the order they appear in the spreadsheet. If you 
copy/paste from an AB scan, AB does not do any type of sorting within 
the same day.  So, the spreadsheet will process them in the order 
that AB  gives them. You can alway do another sort in Excel based on 
date and ticker.
> 
> - It currently only process approximately 3000 rows of Data.
> 
> If anyone has any suggestion for this spreadsheet, please respond 
to the thread.
> 
> HB


Post AmiQuote-related messages ONLY to: amiquote@xxxxxxxxxxxxxxx 
(Web page: http://groups.yahoo.com/group/amiquote/messages/)

Check group FAQ at: http://groups.yahoo.com/group/amibroker/files/groupfaq.html 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/