The Altair Community is migrating to a new platform to provide a better experience for you. In preparation for the migration, the Altair Community is on read-only mode from October 28 - November 6, 2024. Technical support via cases will continue to work as is. For any urgent requests from Students/Faculty members, please submit the form linked here
"Backtested trading system data in excel"
Slyjoker87
Member Posts: 8 
Contributor II
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?
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?
Tagged:
0
Answers
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
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?
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
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?
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