Excel Formulas

[silvermachine 10]

Hi there,

it's been a long time since I've been here.

Is there a forum which discusses formulas for excel spreadsheets for "objective" technical analysis?

 

silvermachine

[forrestang 10]

This isn't technical analysis but a compound growth sheet.

 

I like to create these things and dream...... Einstein knows it's the greatest invention EVAAAR!!!

 

You too can be a millionaire in a short time!!

options_trading.xls

[silvermachine 10]

Interesting.

Actually I use to code in Excel because the formulas are much easier than Java Script.

What I do is to export chart data from my Data Provider to an Excel sheet.

Then I come up with conditions or strategies. The most simple being "buy when interval closes over MA" and "sell when interval closes below MA".

If I come up with a strategy that seems to work I (try to) code it in Java Script so that my charting software gives me signals in real time while trading.

Of course, a trending strategy kills you while the market is moving sideways and chopping around. I'm working on conditions that keep me out of the market while it's not trending.

 

That Spreadshhet shows an example of a stock trading around 75USD in 19 trading days with a 1 minute interval.

I had to delete days because the file was too big to attach.

The strategy is to "buy after 4 higher closes and exit when close is lower than previous' open" and "short after 4 lower closes and exit when close is higher than previous' close".

What I'm working on now is to check different intervals and/or use an exit condition based on previous highs or lows.

 

This strategy did not prove to be profitable in backtesting. It showed a meager profit of $300 in 19 days and a grand loss of about ($2000) in 55 days.

So this is just the beginning.

backtesting higher close smaller.xls

[jamesblue 10]

Hey Silvermachine - great work you've done so far. Are you still working on this?

 

I am trying to do something similar.

 

I want to connect by dataprovider source (BarChart) to Excel and start doing some basic coding.

 

Can you tell me how to get the data source connected to excel? I have tried using the data connection wizard, but it's not working.

 

Do you have a basic spreadsheet already made that I can build off of and just change my data provider source?

 

James

[silvermachine 10]

Hi James,

nope, I've quit on that one.

I found out that automated trading is most profitable when using daily or weekly intervals.

Excel is sure easy to program because of that copy and paste thing. It saves a lot of time.

However with Java Script you have much more options.

That programming took a lot of my spare time. I really like to spend my non-trading time doing simple things, easy things.

The original idea was to have a program that alleviates some of my daytrading stress.

But actually, I am doing really good with my discretionary trading. I beat everything of my past coding and backtesting. So I see little reason in my searching for a holy grail code.

Those overlapping bars in trading ranges with their numerous conditions can drive me crazy. :crap: And in daytrading there are so my fake-breakouts that make my system a loser.

 

Your software should have an Data-Export function where you should be able to save shown data in your desired timeframe and file extension (mostly csv or html). Right-clicking into a chart should do the trick.

I have numerous data spreadsheets of Nasdaq stocks. These are just historical raw data without any coding. They are not update-able.

[ZeroDivision 10]

Can you not use a free online chart software with technical analysis functions? I have been using Openoffice Calc, and

1. it is a pain to get formulas for indicators and code them

2. It gets very very slow with much data

[Jimmy2shoes 10]

I am trying to create a cell, that uses a reference from another cell that says "BUY". I need the cell to say that once this cell says "BUY", literally/theoretically, Buy one share, at price listed in Cell G21. i would continue and hold this until the cell in question says "SELL" then obviously it Sells. However, In the next cell i would need it to calculate the running profit/loss.

 

I would also need the cell to then change the system around so that once the long contract has been sold a short contract now comes into play, and visa versa.

 

I have a Moving Average cross-over system already in place and would love to be able to simply back test it.

 

If anyone would offer any help it would be much appreciated. Also the cross overs have different lengths i.e. 10v20, 20v50,50v100 etc. So would it be possible to weight them so that when the 10v20 cross over(10 above the 20) i buy say 5 shares say. When the 20 goes above the 50 i buy 20 shares and if the 50v100 does the same i buy 100 shares.

 

Any help would be much appreciated thanks.