tag:blogger.com,1999:blog-3007964440702200850.post4406723885959560200..comments2023-11-08T20:39:19.035-08:00Comments on Gödel's Market: Setup Your First MySQL Stock Databaseimplied volatilityhttp://www.blogger.com/profile/13281497787825298368noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-3007964440702200850.post-85926446028840395812015-04-01T13:13:23.687-07:002015-04-01T13:13:23.687-07:00@Elrond Like you mentioned, there are multiple way...@Elrond Like you mentioned, there are multiple ways to do this. For just storing data on multiple symbols I have stored all symbols in a single table with great success. You'll want to put an index on the symbol name/id and you'll probably want to play with indexes on other fields you typically use (e.g. date). <br /><br />Some would suggest using an integer id for the symbol name instead of a char/varchar/nvarchar. Less readable, but somewhat less resource intensive for data access.<br /><br />For multiple indicators, I think the first layout you mentioned is better. It would take up more space (due to extra date and symbol id columns), but it would be easier to manage. <br /><br />The second would be faster if you were going to compare RSI vs MA at a single datetime point. You wouldn't have to join saving you processing/resources. I think it's kind of ugly...but if you have a set of data that you want to run over again and and again with different parameters, you'll save some time by avoiding the joins.<br /><br />Really, database structure comes down to what you plan on using the database for :) I like keeping things easily accessible for general storage. If a backtest I start running would benefit from a different structure (without taking too much time to setup) I'll create that structure and setup the appropriate indexes. Otherwise, I just stick with a semi-normalized design.implied volatilityhttps://www.blogger.com/profile/13281497787825298368noreply@blogger.comtag:blogger.com,1999:blog-3007964440702200850.post-41868705277770337412015-03-31T13:15:23.043-07:002015-03-31T13:15:23.043-07:00Do you have any comments on how to best structure ...Do you have any comments on how to best structure the database when you have multiple stocks and multiple indicators you want to follow? I have always been confused by this. Say I want to keep the monthly historical data for 10 different stocks and 2 different indicators (the RSI, and 12 month MA, plus the closing price). I see a couple of options:<br /><br />1. For each indicator, you would have a separate table. So you would have an RSI table with 10 columns(one for each ticker) and "n" rows where each row would be a date, and the same concept for the 12m MA. Then when you wanted to combine the data you would join the tables.<br /><br />2. You would just have one giant table and the columns would be for example "GOOG Close" , "GOOG RSI", "GOOG MA", etc. for each ticker, and then each row would be a different date. So if you were tracking 3 variables (close, RSI, MA) and 10 stocks you would have 30 columns. <br /><br />Is one layout better than the other? What if you had 100 different indicators...is the answer still the same? I am thinking of this is terms of needing to run a backtest at a later date using historical RSI and MA data (or other data). Elrondhttps://www.blogger.com/profile/16501018802096894560noreply@blogger.comtag:blogger.com,1999:blog-3007964440702200850.post-84772248949885529802015-03-30T11:47:55.289-07:002015-03-30T11:47:55.289-07:00@Michael That sounds interesting and useful. Putti...@Michael That sounds interesting and useful. Putting on my list of topics.<br /><br />@pcavatore Thank you for the suggestions. I will make it a point to address all of these topics whether through the main site or the newsletter. Thank you again!implied volatilityhttps://www.blogger.com/profile/13281497787825298368noreply@blogger.comtag:blogger.com,1999:blog-3007964440702200850.post-5321093922663915002015-03-30T10:02:38.550-07:002015-03-30T10:02:38.550-07:00Very promising indeed....was wondering whether you...Very promising indeed....was wondering whether you can address the below points going forward:<br />-database update automation via R script: how to run a daily task to update prices in the database<br />-database maintenance for stock corporate actions like dividends and stock splits: you may want to check when a corporate action is due and back-adjust the price series accordingly or fully redownload the entire databse from time to time in order to have the adjusted prices series available<br />-cloud solutions like Google Cloud SQL: how to host and maintain a cloud solutionpcavatorehttps://www.blogger.com/profile/15679084215057129073noreply@blogger.comtag:blogger.com,1999:blog-3007964440702200850.post-46945745880026384002015-03-30T06:33:35.172-07:002015-03-30T06:33:35.172-07:00let me know when you decide to show how to downloa...let me know when you decide to show how to download all edgar data into a db program Michael McInerneyhttps://www.blogger.com/profile/12477481022464366728noreply@blogger.com