🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.

Join and Win

"Backtested trading system data in excel"

Slyjoker87

Hi all!

I'm 100% new to the forum and I hope to make the best of the knowledge you guys have.

Before I ask my question, I'll introduce myself and my goals. Skip down if you want to only see my question. First off, I came here after searching for a better way to analyze my stock market signals. Don't worry, I'm not a fool trying to get rich quick by plugging in some data and expecting some miracle prediction. I'm well aware that we have an imperfect data situation in which it is impossible to know with 100% certainty what will happen next (unless of course you are omnipotent!). My goal is to understand the data related to my buy/sell signals better and then cross reference the micro trends, if any, with new signals to get a better idea of probability of success. My signals come from a combination of both technical and fundamental analysis and are therefore entered in manually into a database and are not possible or extremely difficult to represent mathematically.

Thus far, I have coded a very simple macro in excel to check a ticker symbol based on a prediction date and 30 days after.So far, the data I generate includes Max profit potential and profitable value (Boolean value based on if stock reaches a certain percent gain or loss at any time over the 30 day period).

I intend to include the max loss , the date of the max loss, the date of the max gain, and the date of the break even point (meaning not the initial stock price but rather the stock price + a non static gain).

So, should I format the database of results in excel like so?

Id ticker prediction date profitable value max profit max profit date max loss max loss date break even date
-----------------------------------------------------------------------------------------------------------------------------------------------------

Here is a quick Key:

Id: Represents the type of analysis I am doing or the ruleset for a particular signal (basically for separating pure technical from pure fundamental or hybrid)
Ticker : stock symbol
prediction date: the date the signal is generated
profitable value: The Boolean returned to indicate if a stock reaches a certain non static value
max profit: max gain from start price
max profit date: date of the max profit
max loss: max loss from start price
max loss date:date of the max loss
break even date: date of breaking even (if applicable)

What do you guys think? Am I on the right track?

Find more posts tagged with

AI Studio

Excel

Accepted answers

All comments

land

Hi,
it's more or less straight forward and you will get a table providing a good overview, but I cannot see, where you are going to use data mining? If you constructed such a table, have it stored in your excel file or data base, what are you going to do with it?

Greetings,
Sebastian

Slyjoker87

Well, I hope to create several databases with stock information. The table I showed above is simply to collect my stock market predictions. I want to test a database of buy and sell signals I get against certain things like price action.

For example:

I would collect maybe 1000 signals that I enter in manually with dates. I would then use (arbitrary) perhaps 10 stocks that are linked to each stock that I predicted. I would pull 2 years of daily quotes and then run it through some type of pattern finding algorithm to look for patterns that may exist beyond the obvious.

From there, I could then use such patterns to give a better idea when a certain signal has more or less validity.

Does that help?

land

Hi,
I'm not familiar with the stock market terms like signals, daily quotes and so on. But in general your setup should work, but it depends on getting the information available in a table format the learning algorithms can handle. That's a way I cannot draw for you, without understanding your data

Greetings,
Sebastian

Slyjoker87

Well, at the very basic level you have stock quotes. For example, GOOG is the symbol that represents the stock for Google. I am sure you are at least aware that stocks have value attached to them. The data I would use would consist of a date and the opening or closing price of GOOG.

My data that I am collecting consists of buy and sell signals. What this means is that when I get sufficient information I buy a stock. Now, consider that I have a history of these signals and have tested for their accuracy. The data that I collect consists of an ID which tells me what type of rules produced the signal, a stock symbol that was the target of the prediction, the date of the prediction and then true or false value if the signal was correct.

My goal is to compare the raw numbers with my predictions in order to find patterns that are more than meets the eye. I figure I can use multiple sets of data from multiple stocks and even other custom signals to see if they interact.

Does that make sense?

land

Hi,
I think it should work that way if you build a table consisting of one column for the real value and one for each signal. Just note the value of the signal there. Then you could apply a learning algorithm like decision trees to see, which signal and which combinations are most important for the realized value.

Greetings,
Sebastian