Calculating Pivots Points For Day Trading When There Is No Trade Day
Jun 4, 2014
The attached spreadsheet should explain my issue. In a nutshell, the value on line 6 is from the previous day's data (i.e Tuesdays line 6 is based on Monday's data). If the previous day has no data ( i.e. market closed), line 6 should reflect the last line 6 calculation.
Also, i would like line 6 to be zero past 2 calendar days from today. i.e. Friday June 6 should read zero until tomorrow (june5) data is entered.
I have a list of 3D landmarks, and I'm trying to calculate angles and distances between specific combinations of these points.I hit a complete wall when trying to use excel to calculate out those equations. way input these equations?
Distance equation --> distance between points A and B = sqr root of [(Ax-Bx)^2 + (Ay-By)^2 + (Az-Bz)^2]
This person (see the link) posted the excel equation, and I spent a long time trying to follow it, but can't make sense of it! [URL]
I have attached a summary of the excel I have, and exactly what I'm trying to calculate.
Summary:
1. What is the excel equation to calculate the distance between two 3D coordinates (x,y,z)?
2. What is the excel equation to calculate the angle formed by 3 3D points (again, with x,y,z coordinates)?
I have a spreadsheet where I often have to exchange pairs of cells. I currently do this by dragging Cell A to a blank portion of the sheet, dragging Cell B to Cell A's former location, then dragging Cell A to Cell B's former location. I was just thinking that I might be able to use VBA to allow me to drag Cell A to Cell B and have them "trade places".
I don't even know what events are involved in the dragging process so I don't know where to start.
This is what i am trying to do when the balance increases by $1000 the lot size should move to the next level e.g 0>1000=0.01 1000>2000=0.02 and for each movement the value of the 0.01 should be 0.09 e.g. 0.01=0.09 0.02=0.18 0.03=0.27 etc.
I have a number of equity trades (both purchase and sales). I need to know the book cost of those sale trades to figure out the realized gain/loss according to First-in-first-out method.
Since the unit sold may included units bought at different time and different price, so I have to first exhaust the first lot that I purchase before moving into second lot, and so on and so for.
Column G - J are for illustration. I highlighted K10, K11 and K12 are book cost that I want to calculate. Instead of manually separating out units in each lot previously bought. Is there an automatic way? I would not mind adding new columns to ease calculation.
Column A = persons name Column B = number of hours Column C = trade Column D = shift (as in day, night or weekend) Column E = I would like the formula that looks at the trade and time and then puts the correct rate.
I have a little table on the site with trades/times and rates. IF and IF THEN do something
I'm trying to come up with a formula that allows me to insert the date of the previous trading day. Because for several reasons I can't create a column array of manually entered holidays, I'm trying to find a formula that would produce this. I've tried this, but excel doesn't like it.
way to calculate the drawdown for a futures trading account’s equity. The following is the record of taking a sequence of 20 trades. Drawdown is defined as the lowest point between two consecutive equity highs. Therefore a formula should detect pairs of equity highs and then find the maximum difference between these highs and the lowest point in between each of these pairs. Do you think this can be done using standard excel functions?
You might wish to plot a linear graph to visualize the equity. Looking forward to hear what you think! I include more examples of equity records.
formal definition of a drawdown:
Drawdown: The magnitude of a decline in account value, either in percentage or dollar terms, as measured from peak to subsequent trough. For example, if a trader's account increased in value from $10,000 to $20,000, then dropped to $15,000, then increased again to $25,000, that trader would have had a maximum drawdown of $5,000 (incurred when the account declined from $20,000 to $15,000) even though that trader's account was never in a loss position from inception
I'm brand new to programming in Excel, and currently building a system that will help me trade equity options (similar to trading stocks). So far, I've put together the ranking system I want to use based on a live data feed that updates in real-time (during market hours). For every possible trade in my 'universe' (currently about 600), Excel provides a live score (based on my formulas and weightings) as well as prices and other info about the trade (options strikes, symbols, expected return, etc.).
What I'd like Excel to be able to do is keep a separate sheet where a trade (row) that scores over an arbitrary threshold, say, 100 'points,' is recorded as a static line (instead of continuing to dynamically update), like a snapshot of that line. I'll use this list of trades to submit to my broker for trading.
If it was as simple as a 'triggered' copy - paste special - values, that would do just fine. The trigger would also need to see if that trade has already been recorded for the day, so I don't get an infinite list of the same trade.
After a trade makes it into this list, I'll need to compare it to what I already have in my portfolio (which I'll also need to build) so I don't become too heavily weighted in any one trade. (Just thought I'd let you know where this will be going, too) ...
I have 2 separate pivots running off 2 separate data sources. The filters used for each or fairly similar (bar one entry).
Is there a way to have one filter that filters the information in both pivots even though they are not based on the same data source?
Also, with the filter that is slightly different; in one table, I have an option that shows (this is just an example) 'retail and end user' yet, in the other table these are two separate options.
I am working on an excel sheet (2007) with about 8 pivots from the same data (huge db). The 8 pivots take a different cuts of data. One filter element is same across all pivots. (For instance let us say that filter is City names: with 10 different city values in it- NY, London, Chicago etc)
What I would like to do is have a system where I just chose NY in one place and all the 8 pivots should use NY as one of the filters and refreash the data accordingly.
What I am doing now is I got to each pivot, choose NY first and then refreash all for the data.
I am looking for a way to combine three pivot tables in to one. Basically, one sales rep will have data for the current month, the year to date, and the total year. I have to compile this data to one sheet, for 30 sales managers! The problem with running the pivot three times (once for monthly, year to date, and yearly) is that brands that aren't sold in the month are then left off the table, which leads to a mess that it takes far too long to clean up. I hope I've made this clear, but please check the attachments for a better idea of what I'm looking to do...
I have a macro that is supposed to update 3 pivot tables with the change to just the first one but it doesn't work. Can anyone help?
Here is my code but nothing happens when I change the first pt (C & S). It doesn't update the other 3 tables.
Private Sub Worksheet_Calculate() Dim PF1 As PivotField Dim PF2 As PivotField Dim PF3 As PivotField Dim PF4 As PivotField Dim x As String Application.EnableEvents = False Application.ScreenUpdating = False Set PF1 = ActiveSheet.PivotTables("PivotTable4").PageFields("State") Set PF2 = ActiveSheet.PivotTables("PivotTable3").PageFields("State") Set PF3 = ActiveSheet.PivotTables("PivotTable2").PageFields("State") Set PF4 = ActiveSheet.PivotTables("PivotTable1").PageFields("State") x = PF1.CurrentPage PF2.CurrentPage = x PF3.CurrentPage = x PF4.CurrentPage = x Application.ScreenUpdating = True Application.EnableEvents = True End Sub
Pivot Table 4 = C & S this is one I want to make the change to Pivot Table 3 = Sales Pivot Pivot Table 2 = 2005 Pivot Pivot Table 1 = SMG Pivot
how not to show blanks in a pivot table but I do actually want to show them, however they are currently showing as '0' which is misleading to my audience; also I have cells will '*' which I also want to show in the pivot but again these show as '0' - the format of the cells in the raw data in 'number' however I have tried changing this to 'general' or 'text' but to no avail when refreshing the pivot table.
I have 3 sheets in my workbook, DataA, DataB, and DataC
Data A contains: ID_customer and 4 variables A-D
DataB contains ID_Customer and 4 different variable W,X,Y,Z
Data C contains ID_customer and other 4 different variables
Request: I need to analyses the relationship between the variables using a Pivot table.
My thoughts so far: I think that I need to combine all the information onto one sheet first and then use the pivot table function, but its currently unable to combine onto one sheet, due to Id_ customer numbers appearing several times in DataB and DataC (and not always the same number of times in both), I need to retain all the information from DataB and DataC
I have a ton of data coming into excel. I'm making a Pivot table of this.
From this tables everything goes to another sheet for a specific month. Right now January only.
The data will contain several of months later on in the future.
Where I can set the pivot to see the february date and insert into the right cells?
Excel01-file contains the Pivot table setup. Excel02-file is the place where the pivot should insert the data specific to each month taken out from the data file in Excel03_data-file.
I have a spreadsheet with a number of sheets in it for our sales team to record orders received and pending; the first sheet contains pivots which relate to the other sheets (a sheet for each financial quarter) (i.e. one pivot for sheet 2, one pivot for sheet 3 etc)
Is there a macro which will allow me to update all of the pivots when the workbook is opened? Or, even better, one where they will be updated when the first sheet is accessed?
I have a monthly sales report with x amount of customers in a number of countries with an ABCDE mark. I have made a pivot table with ABCDE marks in rows and countries in Columns.
I have then grouped the countries into regions. Looks something like this: [URL]
If you can't see it look here: [URL]
Now my issue is that I have to compare many months of this kind of data to see movement. What I wanted to do was to display these changes but how do I do this without having to create 10 different pivot tables, and is there a way I can save the country groupings so I don't have to manually group the countries for every pivot table?
The below code and variances of it has always worked for me when controling he pivot fields, however this no longer works in excel 2010 and i cannot seem to come up with a work around.
Code: Sub Pivot_Date() Application.ScreenUpdating = False
Dim pvtTable As PivotTable Dim pvtField As PivotField Dim pvtItem As PivotItem Dim filterCell As String
[Code] .......
Its worth noting that this will work wen selecting all but not for individual fields. I have also tried skipping the loop ad simply setting the current page to the ilter cell but this doesn't work either.
My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet. How can I do that?
I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editiing. how to enable this
For example: - I have 3 pivot tables with the same row 'Media' (TV, Neswpaper, Magazine) and each has count as the data field (number of people who read/watch) then each pivot has a different column, Region, age, ***.
If I change the row of the first pivot to only show TV, how do i code it so pivot 2 and 3 only show TV also? I may have upto 20 pivots inthe end.
I found the below code to do this for page fields but can't adapt to rows -
HTML Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim ptTable As PivotTable, ptItem As PivotItem, vFields As Variant, lngField As Long On Error GoTo ExitPoint vFields = Array("filed1", "field2", "field3")
Application.EnableEvents = False For lngField = LBound(vFields) To UBound(vFields) Step 1 For Each ptTable In ActiveSheet.PivotTables If ptTable <> Target Then With ptTable.PivotFields(vFields(lngField)) .CurrentPage = Target.PivotFields(vFields(lngField)).CurrentPage.Value End With End If Next ptTable Next lngField ExitPoint: Application.EnableEvents = True End Sub
