Support Board
Date/Time: Mon, 25 Nov 2024 17:27:51 +0000
[User Discussion] - Backadjusting spreadsheet database
View Count: 1598
| [2014-01-26 23:19:20] |
| enemyspy - Posts: 306 |
|
Hi, this is probably not supported but just wondering if anyone knows the answer: I have copy and pasted a longer term data base of certain intraday stats into excel which I keep updating for further analysis manually, would anyone know of an way/formula to back adjust the prices every quarter, without re-copy and pasting a whole bunch of stuff? |
| [2014-01-28 01:25:42] |
| vegasfoster - Posts: 444 |
|
You can do anything in excel, but I need more specific details to be able to help you. It would be easiest if you would post the file so I can see it.
|
| [2014-01-28 02:00:57] |
| enemyspy - Posts: 306 |
|
OK..it is still very crude, I will clean it up a bit first and then post.
|
| [2014-01-28 20:08:10] |
| vegasfoster - Posts: 444 |
|
Ok, but it doesn't have to be pretty, I just need to know how you have it organized to determine the best approach. If you have other formulas, etc. you want to add then I can help with those too. :)
|
| [2014-02-01 21:45:59] |
| enemyspy - Posts: 306 |
|
Ok, I apologize for the delays. I have been insanely busy recently and really appreciate your willingness to help with all this I will post it as soon as I get home today it will not be pretty, and I have determined that I need to readjust the time frames to tick charts for certain reasons but the template will basically remain the same..
|
| [2014-02-02 08:03:25] |
| enemyspy - Posts: 306 |
|
Ok here is the first file. If you refer to the sheet titled "M5 STATS" you will see the prices in column C. That is currently the only part that will need to be back adjusted regularly. As you can see most of it is copy and pasted data from a shorter term SC sheet. I have not had a chance to add the additional formulas that take the entire data set into account yet. I have more spreadsheets tracking different things that will also need this, but they are currently still only in sierra chart. This sheet is still incomplete. I need to to change every time frame to small range bars for greater precision in the datan also. I really appreciate the help with this. I do have more that is not transferred out of sierra chart yet, and your help is very appreciated. Date Time Of Last Edit: 2014-02-02 08:09:11
|
 Swing range stats draft1.xlsx - Attached On 2014-02-02 07:55:37 UTC - Size: 1.95 MB - 525 views |
| [2014-02-02 08:12:30] |
| enemyspy - Posts: 306 |
|
something I was also wondering about is adding some kind of news events column in order to classify the swings into seperate data sets. for instance a tag for "fed day" "ISM #s" "retail sales" ect...is there any news service out there that provides something like this organized into spreadsheets?
Date Time Of Last Edit: 2014-02-02 08:13:35
|
| [2014-02-04 22:26:58] |
| vegasfoster - Posts: 444 |
|
I've got another project I'm working on for someone else today and then I'm planning to get to this tomorrow or Thursday. I have a news calendar someone made, but I have not ever loaded it with a spreadsheet, but something I can look at.
|
| [2014-02-07 18:55:45] |
| vegasfoster - Posts: 444 |
|
So I built a new computer a few weeks ago and now I can't get the events downloader to work on my machine. I attached it if you want to play with it. I'll keep working on it. 1. Install Java, if you haven't already. 2. Unzip the EcomonicEventsDownloader_v5.1.zip file into a convenient folder. 3. Open the folder and double click the EconomicEventsDownloader_v5.1_executable file. 4. This should create a HistoricalEconomicEvents.txt file. 5. Put the EconomicEventsDisplay.dll file in your SierraChart\Data folder. 6. Add the custom study to your chart and change the "File Containing Historical Economic Events" path to the path of the HistoricalEconomicEvents.txt file. That said, I added the study to my chart even though I don't have a txt file to point to, and it does not have any subgraphs, which means in it's current state it will not output to spreadsheet. If I can get the downloader working, then I will see if I can modify it since this would be extremely useful for my own purposes as well (a project I have been planning for while, but never seem to get to :)). I have taken a look at the spreadsheet, how do you want to adjust the price, simply shift all of the prices up or down a specified amount? |
| EconomicEventsDownloader_v5.1.zip - Attached On 2014-02-07 18:51:27 UTC - Size: 26.26 KB - 507 views EconomicEventsDisplay.dll - Attached On 2014-02-07 18:52:09 UTC - Size: 724.5 KB - 466 views |
| [2014-02-07 18:58:43] |
| vegasfoster - Posts: 444 |
|
If you (or anyone reading this) can get the downloader to work and post the txt file here that would be helpful.
|
| [2014-02-08 08:33:37] |
| enemyspy - Posts: 306 |
|
I have uploaded the text file. It would be definately be very useful if the viewer could be modified to have spreadsheet outputs. As far as the back adjusting goes I would like to be able to quickly enter in the amount that sierra chart back adjusts every quarter into one cel and have it apply that to all the prices that correspond to the previous dates accordingly. Perhaps a seperate cel for each contract so that it affects all prices for dates occuring before the contracts rollover date, that way it would also remember previous backadjustments. Unless you can think of a more efficient or better way? Also if that is how backadjusting works? |
Attachment Deleted. HistoricalEconomicEvents.txt - Attached On 2014-02-08 08:23:53 UTC - Size: 1.17 MB - 561 views Attachment Deleted. |
| [2014-02-08 08:34:03] |
| enemyspy - Posts: 306 |
|
thank you for that tool as well
|
| [2014-02-12 15:07:46] |
| vegasfoster - Posts: 444 |
|
Sorry, got obsessed with my own thing. So, couple questions. Are you updating this daily? Is this already backadjusted and you just want to be able to update going forward? My initial concept was to create a macro that will update the prices based upon a figure you enter. Right now I have it set so that you enter the amount of the adjustment in cell T8, then click the "Adjust Prices" button, select the range you want to adjust, click ok. You can select ranges on other sheets and it will automatically return to the "m5 stats" worksheet so you can do it all over again. If you are going to be adding/deleting sheets and or making other changes to where the data is located, then something like this is the best option. If you get everything to the point where you aren't making any more changes, then I can make it so it updates the entire workbook all at once. Date Time Of Last Edit: 2014-02-12 15:08:54
|
| Swing range stats draft1.xlsm - Attached On 2014-02-12 15:07:40 UTC - Size: 2.09 MB - 510 views |
| [2014-02-18 20:52:29] |
| enemyspy - Posts: 306 |
|
Thanks for this. Much appreciated. I have been focused on other things lately and have had no time to further my spreadsheet studies recently. Hence the absence from this thread. I have just started to look at this now and I think it will work well. I will be updating daily, and the sheets still need to be structured further so I do appreciate the way it is set up. Yes it is based off of pre-backadjusted data that needs to be kept properly adjusted going forward. A general question about backadjusting: when a new contract back adjusts old backadjusted contracts. Does it simply move all of the backadjusted contracts up or down the prescribed amount while taking the pre-backajusted amounts into account? |
| [2014-02-19 18:11:15] |
| vegasfoster - Posts: 444 |
|
The backadjusting simply adds or subtracts the current adjustment from all previous values, because it assumes the previous values have already been adjusted. If not, then you would need to manually adjust those first, or simply export the data again using backadjusted continuous contracts, and then you can use a macro going forward.
|
To post a message in this thread, you need to log in with your Sierra Chart account: