Free Microsoft Excel 2013 Quick Reference

Recreate Trendlines From Stock Trading Software

Hi!
I need your advice. I am trying to recreate trendlines drawn in a stock trading software. I have been able to do this almost exactly for small-denominated securities (e.g. EURUSD, EURGBP) whose price ranges between 0 and 2. But with larger denominated securities like QQQ and NK (whose value ranges in tens and thousands) I get progressively bigger divergence between the trendline plotted in the charting software and my own trendline recreated from the price data. I have attached the workbooks for all securities mentioned above. Each workbook contains 4 columns of data exported form the charting software. The e column which follows shows where the trendline starts and stops in the charting software. The formulas in the H to J columns calculate linear trendline equation using the SLOPE and the INTERCEPT functions. These are then used in the K column to create my version of the original trendline. The final L column calculates the difference between the original and recreated trendlines. Now my question is why the quality of the trendline tends to DETERIORATE with the price scale of the commodity INCREASING?
Thanks!!!
Here is the link to the zip files:
rapidshare.com/files/125236117/trendlines.zip.html

Post your answer or comment

comments powered by Disqus

Related Results

Stock Trading Activity to Sch D format with Pivot Table

Hello,
I have a stock trade database with the following fields.
1..Date
2..Action—(is Buy or Sell)
3..Quantity
4..Description
5..Unit Price
6..Total Amount
Buy amount is always negative and Sell amounts are positive

How would I set up a pivot table to get the following format by fields (a schedule D format):
1..Description
2..Quantity
3..Action
4..Buy Date
5..Sell Date
6..Buy Amount
7..Sell Amount

For example-- Input data
Date...Act...Qty..Desc..UnitPx..TotAmt
Jan 1..Buy…3…TypeA..-10........-30
Jan 3 Sell…3…TypeA....17.........34

Desired output:
Desc:.Qty.Act..BDt..SDt..BAmt..SAmt..Net
TypeA..3..Buy..Jan1..xxx…-30…xxx
TypeA..3..Sell..xxx..Jan 3..xxx….34
Total……………....….......……..-30….34…....4
the "Net" field in the output is calculated. "xxx" means no entry ie blank
Thanks

JB

Downloading data into Excel

I have a simple query about downloading tabular data into Excel. Specifically, Id like to download streaming stock data into an excel worksheet from my trading software. Is there a way of doing it, if the trading software doesnt allow a DDE connection?

thanks

Getting a constant value from a Link (by RTD)

Hi,

I have many values that I retrieve from a trading software by RTD link (I can also retrieve it by DDE Link).

The Value that I retrieve is an Index value that changes about 2-3 times a second.

I would like to retrieve this values of the index for many cells, but just for the question matters lets say I would like to get the information for only one cell.

The way I would like to get the index value is only at a certain time. I've tried to using IF condition but it's all mass up, meaning:

If the time is 10:00 then cell A1 get the value.

The problem is that there's the "Else" part of the IF which I have to fill in, and when the time is 10:01, the cell get the value 0 (since it's no longer 10:00).

I'm sure that by VBA I can solve this, but unfortunately I do not know programming by VBA.

Can you think of a way to help me solve this please ?

P.S.1 I use Excel 2007.
P.S.2 For all it matters, The fact that I'm using RTD (Or DDE) link doesn't need to bother to the question.
The fact is that there's a cell that changes once a second (even more often..) and the need is to get a value in a certain exact time, and that this value will remain constant for the rest of the day without change even though the time will change after that second...

Thank you.

how to summarize net number of stocks traded (buy and sell)

I want to summarize net number of stocks traded by each trader. How to use VBA to do so? How to deal with buy and sell to get the net number? A sample is attached.

Thanks

Help with starting a macro with a change in DDE time value?

Hello,

I am new to this forum and also to VBA coding. In general, I would describe myself as an excel novice working toward intermediate. My interest in excel has risen due to the fact that I am learning how to trade currencies and I am in the process of setting up a worksheet to monitor my trades, risk per trade, performance etc. In doing so I have come across a hurdle that I am not sure how to resolve. Therefore, I will describe what I am trying to achieve in the hope that one of you may be able to offer some assistance:

A section of my spreadsheet imports information such as opening prices and closing prices from proprietary trading software via a DDE data feed. In turn, I make numerous calculations on these values. The issue that I have, is that at 00:00:00 broker time, the server switches to the next trading day. What I would like to be able to do is run a macro to copy and paste a range of fixed variable cells and paste special, values only, of a range of cells containing either formula or DDE imports to a separate worksheet in the same work book. I would like this macro to run when the time (which is also a DDE import: format: ='MT4'|TIME!GBPUSD) is equal to 23:58:00.

This would then give me a hardcopy of all of the values just prior to the end of the days trading.

I hope that I have clearly explained my question and look forward to any help that you can give.

Regards

Oneday

How to do auto sorting

Hai guys and girl...
I need some help on excel auto sorting. As you can see from my workbook on sheet 1. there is 5 header on different rows. this rows wont be change unless for the row of g3. this rows will get autoupdate of price from my trading software. And in sheet 2, here come the problem i get. I try to get auto sorting the price from largest to smallest. And the currency pair will move along with the profit and loss next to it. So it like all the positive will stay above and negative will stay below along with the currency pair.

Your help is appreciate.

Live Feed Help.

Hello all,

First post here and also a new Excel user so please forgive me if I use incorrect terminology! Ok, so here goes. I'm trying to teach myself how to trade forex, and as part of my ongoing attempts to become profitable I am trying to analyse some of the price data in excel. I've managed to set up a live feed into Excel from my trading software using a DDE? Which works and is great but it just gives me current prices for the currency pairs and that's all.

So, my question is - Is there a way to take data from the live feed say, every hour, and form a table? Like a table that shows the prices for the last 24 hours and constantly updates itself? I'll leave it there for now and see if you guys and girls have any ideas. Thanks in advance,

Sam.

The ultimate stock trading data model

Just looking for more feedback on a very forward looking OPEN SOURCE
data model. XL traders could really help us out here!!!

(see below for the general dialogue going on)

http://www.itsdoc.org/tiki/tiki-inde...20Requirements
(see the discussion section)

(main site is www.ITSdoc.org)

Thanks!

Rick

From Garth
Hi folks, couple of thoughts.
1. I think that the choice of development lanaguage has already been
made for us. Since we all want to go the open source route the only
sensible option would be JAVA. My rationale for this is:
a. Cross platform (runs nice on linux)
b. Volume of Developers
c. Libraries, middleware, etc.
2. I agree that we need the realtime AND end of day system.
3. The scope of the project will depend alot on the type of user we
are targeting. My beef with alot of investment software is that there
is an arbitrary divide between "consumer" and "institutional"
investor. To be fair, the institutional investor does have greater
needs (corporate compliance and integrating (with other systems))
HOWEVER all the needs of the corporate user i.e, risk management,
portfolio management are just as CRITICAL to us as "consumer"
investors if not more so.

----
More from Garth

Another thing that I would like to add to our functional requirements
is a module that can calculate yield given commissions, leverage,
interest rates etc. A combination risk managemnt and portfolio
managemnt. I have already done some work on this and it's not as
daunting as it first appears.

----
Rick to Garth

Java is a great choice. Still may have to deal with some mixed code
in various parts of the system. Hopefully the messaging system will
accommodate bridging some applications in different languages.

Major role for Excel as a programmable user interface, data entry,
reporting, charting, etc.

Real-time and end of day data systems don't have to be concurrently
developed. Still, its a great idea to allow the design for both to
work together in the future. Especially combining time buckets into
days so the real time system will mesh with the end of day/week/month
stuff.

Target user (proposed for discussion): One person, managing 100
separate accounts, with securities in 100 different countries,
average account size 1 million dollars U.S., for total assets managed
100 million dollars. Also fully usable by a mutual fund that manages
ONE account, of 100 million dollars, across 100 different countries.
Also fully usable by a hedge fund that manages 100 million for three
investors, long short, with derivatives, 100 different types of odd
fixed income paper, lots of FX and all types of exchange traded
commodities.

The above is to set the design direction that this ultimately is "a
single person aircraft." No need for co pilot, radio man, navigation
officer, several stewardesses, full ground crew, etc. Push off from
the gate and fly this thing well all by yourself.

On the other hand, it is a VERY capable system. Designed from the
ground up to be a turret of action whenever the market becomes
exceedingly tumultuous. It has the capability of dealing very well
with 5 million dollar transactions (5% of a \$100 million portfolio)
in a single ticker without the investor getting screwed.

---

Rick Replys to Garth
-------------
>Hi folks, I am really enjoying this discourse. It is good to have
like minded individuals to bounce things off. I agree with everything
Christian said. The Data model is crucial. I have been thinking
through the data model and here are some of my thoughts. Feedback
appreciated.

>Have a planet class, then country class, then exchange class, then
financial instrument class, then data point classes (for intraday
data points and end of day). Obviously, we will have to fill out the
attributes and create other classes via inheritance. I think that we
could use Hibernate to manage the object-relational mapping to mysql.
This could be done fairly quickly. Just some thoughts to get things
rolling. An all open source data layer !!!

Planet Class
------------
One of the best ideas yet! Not because we intend to trade with
extraterrestrials but to illustrate to everyone we want some "room"
designed into the system to possibly use later. We are going for 20
year forward foundation code, so lets leave room "on both ends of the
string" for expansion. Remember when Bell made telephones, they put
in 4 wires when they only really needed two? When you wanted the
Princess Light those two wires came in handy!

Country Class
-------------
Would prefer this to be a "geodemographic" class with latitude,
longitude and boundary vectors. Remember, in the United States you
have States, Cities, Towns, Counties, Municipalities, Zip Codes,
Census Tracts, Sales Tax Jurisdictions, Private Toll Roads, ect all
which overlap and follow no rules. Why is geography important?
Commodities are delivered, unloaded to a specific port, firm names
can be duplicated (same name but different firms in two countries,
etc.) Also emerging countries boarders can change. Hell, large
established countries boundaries change. Debt can be issued by a firm
or municipality, or project. Things like SEC fees may be due on U.S.
transactions in listed securities, even if they are exchanged across
just a bulliten board communication network. Etc.

Plus, in doing research you might want to know "odd things" involving
geodemography. For instance which insurance company (or reinsurance
company) will be hit hardest after the hurricane. (Requiring you to
be able to search company records as to who they insured, map that to
geodemograpic coordinates, establish the likely geographic track of
the hurricane, estimate damage, etc. Also, similar, estimate the
damage to oil refining capacity in the U.S. using same technique,
find oil facilites by zip code, translate to zip code centroids (or
better yet map facility by satellite photo to lat/long coordiantes),
plot the hurricane probability paths, estimate the capacity
reduction. Another key data point to have for each facility would be
the VERY CRITICAL "feet above sea level" statistic, building
earthquake rating, etc. All this just illustrates the need for a good
geodemographic data structure.

See also:
ISO 3166 Domicile codes codes for countries and
subdivisions
ISO 4217 Currency codes Currency codes

Exchange class
--------------

Not only physical, organized exchanges but also Electronic
Communications Networks (ECN), Auctions and other markets. Some of
these do NOT map to a geographic point.

http://www.itsdoc.org/tiki/tiki-inde...xchange%20and%
20Market%20List
ISO 10383 Codes for exchanges and market identification (MIC)
http://www.iso15022.org/MIC/homepageMIC.htm Codes for
exchanges and market identification (MIC) wiki page excellent

Financial instrument class
--------------------------
We don't need to do too much orginal thinking here...."except" ...

http://www.fpml.org/
ISO 6166 International securities identification numbering
system (ISIN)
ISO 10962 Classification of Financial Instruments (CFI code)
http://en.wikipedia.org/wiki/ISO_10962

The design should allow the derivatives and underlyings to be
built "like tinker toys" - snapped together and taken apart. The VaR
module and portfolio must be able to "see" what the combinatorial
possiblities are. For example the data structure must support the
ability to see all "Synthetics" you have created in the test
portfolio or the impact of adding a certain derivative:

Some Synthetic Examples
-----------------------
Long Call - long put and a long stock or future
Long Put - long call and a short stock or future
Long Stock - short put and a long call
Short Call- short put and a short stock or future
Short Put - short call and a long stock or future
Short Stock - short call and a long put
Straddle - Futures and options combined to create a delta neutral
trade
Underlying - long (short) call together with a short (long) put. Both
options have the same underlying, the same strike price and the same
expiration date

ISO 19312 Financial Instrument Attributes and other Market Data
Model (Extended with 20022)
ISO 20022-1 UNIversal Financial Industry message scheme - Part 1:
Overall methodology and format specifications for inputs to and
outputs from the ISO 20022 Repository Repository where processes,
messages and the data dictionary are modelled together using the
Unified Modelling Language (UML)
ISO 20022-2 UNIversal Financial Industry message scheme - Part 2:
Roles and responsibilities of the registration bodies
ISO 20022-3 UNIversal Financial Industry message scheme - Part 3:
ISO 20022 modelling guidelines
ISO 20022-4 UNIversal Financial Industry message scheme - Part 4:
ISO 20022 XML design rules
ISO 20022-5 UNIversal Financial Industry message scheme - Part 5:
ISO 20022 reverse engineering

data point classes (for intraday data points and end of day)
-------------------------------------------------------------

Need a Time Class
Note at its most detailed level we probably need 1/100's of a second
or more to properly sequence bids/offers/transaction in a very highly
traded security during a hugh volume day. Our system needs to be MUCH
better than typical exchanges (which report only Seconds Since
Midnight and use a propiretary sequence number that is never made
public, as a result only THEY and a court order can see who exactly
had priority.)

Need to be able to store "tick by tick" data, timestamp, change in
any of the following: bid, bid volume, offer, offer volume,
executions, execution size, limit orders, special order instructions
(fill or kill, all or none, etc) plus others.

Need a way to consolidate tick-by-tick data into "bars" or time
buckets. E.g. Hi/Low/Last for 5 minutes, 10 minutes, hour, day, week,
month, etc.

Obviously, we will have to fill out the attributes and create other
classes via inheritance.
----------------------------------------------------------------------
---------------------

Need an Entity class for financial reporting

XBRL eXtensible Business Reporting Language (Corporate business
reporting) http://www.xbrl.org/
SWIFT ? Tends to deal mostly with settlement, ISO authorized for
Bank Identifier Code see ISO 9362, 15022? http://www.swift.com/

Need a map from Reporting Entity to Financal Instruments issued
and/or traded (derivatives)

Need a messaging class?
See ISO 20022

I think that we could use Hibernate to manage the object-relational
mapping to mysql.

I'm don't have a great background here but was thinking we should
have an "Industrial Strength" messaging layer in the architecture
between application modules and persistent storage.

Other thoughts:

MySql - nice stable db but how about a "connector" so multiple
databases can be easily hooked up to. For example
http://adodb.sourceforge.net/ Must be something very similar for Java?

This could be done fairly quickly.
-----------------------------------

I'm hoping we move a bit more slowly, especially when laying in
cornerstones of the artecture. If people are really just itching to
run forward they should take just a small section and run (for
example - arbitrage commodity to underlying stock using end of day
data. Just go for it as a separate project and try to make some \$\$\$,
hell that's what this is all about!!!) Keep the eye on both balls 1>
the current "make a quick buck" stuff (which by the way will be very
helpful testing out some of the longer term areceture stuff) and 2>
the longer term 20 year project.

Just some thoughts to get things rolling. An all open source data
layer !!!
----------------------------------------------------------------------
-------------

Excelent thoughts! Open discussion of the issues is the only way we
will ever get close to getting this right.

Rick

More ...

http://www.itsdoc.org/tiki/tiki-inde...20Requirements
(see the discussion section)

Stock Download Code

I have found a code to download data from internet from this site which I have modified to download stock data from nseindia.com

I need come modification to the code to suit my needs:

1. I have copied and pasteed the url from the address bar while downloading data for a stock code "sail" from nseindia.com. This can only download data for one stock code "Sail" & that too for the period 01/01/1994 to 30/06/07 because the code contains these as fixed element. In case I need to change the stock code of "Sail" & date range I have to change the code itselt. Can these -
Stock code &
date range be made variable

I have attached a sample wb which will show these requirements.

2. Secondly, I am using a stock charting software which supports csv files and the csv files can be imported directly to that software.So, Cell I1 which shows Total traded quantity needs to be changed to "Volume" automitaclly as the charting software recognizes term "Volume"

3. Can the code be modified in manner whwreby the downloaded csv files will be automatically saved to a folder say D:prices

In case some more clarification needed , plz let me know.

I have also posted the same at vbaexpress.com but not getting any reply.

http://vbaexpress.com/forum/showthread.php?t=13150

Hope, here i'll get some help.

Live updating A csv file from another worksheet

Friends,
I am in stock trading business. My broker provide me live data in a worksheet which goes on updating as the market moves. This worksheet can be exported as csv file. These data in csv file need to be further formated for using them in a charting software. Is there any way to keep on live updating the csv file from the first worksheet.

Capital gain through stocks on FIFO Method

Dear Sir,
please help me for calculation of capital gain through stocks on FIFO Method . i had attch my stock trade book file for your reference. it will be very grateful as well as very helpful for me if you help me by adding any formula or any macro function which will calculate capital gain for different stocks base on FIFO method. in my attach file i had attch CP WORK SHEET which i had downloaded from boker website.
so the program should not only calculate the profit by subtracting the cost of shares on FIFO basis (that is identify the earliest existing stock). but since the data does not readily give me the correlation between the purchases and sales on FIFO basis

i want to know how CP calculation base on FIFO Mehod by first considering sale qt, sale date, sale price then purchase qt base on sale qt, purchase date, will be done in excel file base on Trade book data.

I had uploaded file in discussion forum.

Thanks a lot in advance

Thanks

Sunil

Get error when I remove some data from source data?

I have a combination of macros & worksheets I have been using for over a year. They work great. However, recently I tried removing some of the data from the spreadsheet I am running the macros on & I get an error message.

How the process works is:
1. I take stock trade results for each individual stock (that is the spreadsheet) & I run the macros on that particular stock.
2. The macros match the dates of that stock's trades against a master list of dates for that same time frame and they put zeros where there were no trades on that date & they put the trade results for that date when there were trades on that day.

Where I am running into problems is when I remove the "Sell short" trades from the list & then run the macro with those removed. It gives me a "run time error 1004". When I open up Visual Basic the problem is with " If Format(cell.Offset(-1, 0), "mm/dd/yy") = Format(cell, "mm/dd/yy") Then".

I am attaching the files I am using. Here is how I use the files & macros:
1. Open all worksheets (labeled as follows).....
"AACC" (this changes evertime, this is the individual stock I am testing)
1-SAMPLE STOCK"
"Macros for dates 2000-2004"
"THIS ONE DOES IT ALL IN ONE STEP"
2. I cop the entire worksheet of the stock I am going to use (in this case "AACC") & paste it in "1-SAMPLE STOCK"
3. Then I run the macro titled "THIS ONE DOES IT ALL IN ONE STEP.xls!rtaao" -this takes my stock (AACC) & matches all the trades up with the master list of dates in the "Macros for dates 2000-2004" worksheet

That entire process works perfect. The problem is when I take the "AACC" file (which is a different stock each time) & I remove all the trades titled "Sell Short". Then I try steps 1-3 (listed above) & it gives me the error.

I have attached the file labled "AACC-Longs only" which has the sell short trades removed. When I copy those trades & paste them in the "1-SAMPLE STOCK" worksheet (Step 2) I get a "run time error 1004". When I open up Visual Basic the problem is with " If Format(cell.Offset(-1, 0), "mm/dd/yy") = Format(cell, "mm/dd/yy") Then".

If anyone can help me resolve this I would greatly appreciate it. Thanks in advance.

PS-I have attached the two source data files (the original) AACC & (the one with the sell short trades removed) AACC-Longs only.

I tried to attach the others files on following post(s), but they are to big to upload. If someone can help me with this I will email you the files. In the meantime I am posting the macro code here:
```
VB:
rtaao()

'ZZZZZZZZZZZZZZZ()

Dim g As Long
Dim c As Range
Dim d As String
Dim cell As Range
Dim mw As Workbook
Dim ss As Workbook

Application.ScreenUpdating = False

'NOTE I"VE MOVED THESE TO HERE. CHANGE NAMES AS NEEDED...

Set mw = Application.Workbooks("Macros for dates 2000-2004.xls")
Set ss = Application.Workbooks("1-Sample stock.xls")

ss.Activate
Range("\$A\$1:\$A\$4").EntireRow.Delete

Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("E:F,H:M").Select
Selection.Delete Shift:=xlToLeft
Range("A65536").End(xlUp).Offset(1, 0).Select
d = ActiveCell.Offset(-1, 0).Address
Range(ActiveCell, "a65536").EntireRow.Delete

'Clean

For Each cell In Worksheets("Sheet1").Range("C2", d)
If Format(cell.Offset(-1, 0), "mm/dd/yy") = Format(cell, "mm/dd/yy") Then
If cell.Offset(0, 2) = "n/a" Then
cell.Offset(0, 2) = 0
End If
cell.Offset(0, 2) = cell.Offset(0, 2) + cell.Offset(-1, 2)
cell.Offset(-1, 0).EntireRow.Delete
End If
Next cell

g = Worksheets("Sheet1").Range("c65536").End(xlUp).Row

Range("A1:E" & g).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Copy To Macro sheets

ss.Sheets("Sheet1").Columns(3).EntireColumn.Copy Destination:=mw.Sheets("Sheet1").Columns(7) ' was 2
ss.Sheets("Sheet1").Columns(5).EntireColumn.Copy Destination:=mw.Sheets("Sheet1").Columns(3)

'Activate macros workbook

mw.Activate

'Set Column G formula

g = Range("G65535").End(xlUp).Row
Range("H1:H" & g).Formula = "=int(G1)"

mw.Sheets("Sheet1").Columns(8).EntireColumn.Copy
mw.Sheets("Sheet1").Columns(2).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("B:B").Select
Selection.NumberFormat = "0"
Range("a1").Select

'Insert Rows

Set c = Range("A1") 'set to starting point of all dates

While Not IsEmpty(c)
If c.Offset(0, 1)  c Then
c.Range("B1:E1").Insert Shift:=xlDown
c.Offset(0, 1) = c
c.Offset(0, 2) = 0
End If
Set c = c.Offset(1, 0)
Wend

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Let me know & I will email the full files.

Parse information from text file to excell

I am having a problem with parsing information from a text file to my sheets.

I hope this isn't to complicated....

I have a text file with thousands of lines of stock trades. These are formated as shown below. The problem is there are new trades coming in. So what I need help with is some code that based on the trade code will associate with the correct trade.

(Trade codes are (O) Open, (C) Close, (P;O or P;C) Partial Open/Close)

Stock Date Shares Price Trade Code

LOW 7/28/10 11:37:07 AM 16 20.98 -1 0 -4.48 O
LOW 7/28/10 1:40:56 PM -16 20.82 -1 -4.56 1.92 C
LOW 9/2/10 3:27:23 PM 115 21.98 -1 0 -4.6 P;O
LOW 9/28/10 9:30:48 AM -115 22.2 -1 23.3 -24.15 P;C

So with the above information it would parse in the following format.

Line 1 is as follows showing the trade is Opened.
LOW 7/28/10 11:37:07 AM 16 20.98 -1 0 -4.48 O

Line 2 shows the trade is closed. and is appended to the first trade. The example continues with the last two trades. I am using standard Tab formatting although I can change it.

LOW 16 7/28/2010 \$20.98 7/28/201020.82 (16) 2.00
LOW 115 9/2/2010 \$21.98 9/28/201022.20 (115) 2.00

Importing: Stock Market Scanner

For years I have been importing a web page into a spreadsheet. I wrote a macro to automate the importation of data from a web site and then had the data automatically manipulated and sorted to assist me in making stock trading decisions.
The web page is from a stock quote service called eSignal. They are a pay service that normally requires a username and password. Even when I'm logged in, I still can't get Excel to get the external data using web query. Although this has worked for me for two years, eSignal has told me that they upgraded their servers for "better security". I can recognize no other changes except that Excel's requests for the data now result in a message from eSignal's servers that says, "You do not have access to this page - please contact customer support." Other than this message, Excel's import does work. It still imports the page - just without the necesary data. I am stumped as to how to get Excel to read web data when a username and password are required.
The file I am trying to read is a ".asp" file. The url starts with: "

Macros For Stock Trading

Hi,

I am a new entry here.
I will be joining one of the Investment banks as a trader.
(graduate programme)

Unfortunately, i'm not too aware as what I should know about Excel/VBA for trading...can anyone shed some light as to the usage, technical skills I should know? From what I should start...etc? (I have half a year before I start work)

All I know at this point are:
* usage of formula
* pivot table
* charting
* basic spreadsheet usage
* basic conpect of the usage of macros

Thanks for any advise,

Regards,

Zz

Reinvest trading p&l into next position, whilst maintaining parameters

Hi,

I hope this makes sense; and that someone can assist me.

Attached is a spreadsheet i'm working on with regards to a back tested stock trading strategy.
Hopefully what's in the spreadsheet makes sense, my issue is that I need to apply whatever the p&l is from the previous position and then apply that to the next open position.

Now based on the book system as described in the s/s, 1 book would typically be £4,166.67, leveraged 3.5 times =£14,583.33, and applying 10% margin requirement for a position out of the book = £1458.33. I BUY 24.7 CFD contracts of BATS @ 590.39, total value of £14,582.63 = 10% margin to hold this position would be £1,4759.75 and incur a commission charge of £36.46 on the total value. The position is then closed out 6 days later @ 598.67, once both commissions on open and close were applied plus financing for the holding the position net profit = £129.95.

I then open a new position on the day I closed the BATS position, but the 'book' now needs to be adjusted by £129.95 - so £4,166.67 - 129.95 = £4039.72 * 3.5 leverage = £14,139.02 *.10 = £1.413.90 for the next position... and so forth and so forth

I hope this makes sense! If anyone can point me in the right direction I would greatly appreciate it as I'm racking my brains and really don't know where to start.

I've attached the data with the first position as described as above, hopefully this gives you a good idea of my predicament.

Thanks you for reading and hopefully someone can help.

Attachment - http://www.sendspace.com/file/0sx5l6

Stock trade - time, price and volume graph

Hello

I'm using Excel 2002 and trying to create a chart to illustrate the price, volume and time (not date) of a number of trades. The chart should have 'time' on the x-axis, and 'price' (line chart) and 'volume' (bar chart) on 2 y-axes. The problem with the custom chart called 'Line-column on two axes' is that it does not recognise the x-axes as a time-series i.e. if you graphed 10 trades occurring over an hour period and 8 trades occurred in the first 5 minutes, then 80% of the x-axis would be taken up with the first 5 minutes.

I could create a second spreadsheet containing every second of the trading period (A1=08:00:00, A2=08:00:01, A3=08:00:02 … A32400=16:59:59) and use VLOOKUP to slot the volumes and times against the correct second, but this leads to some very cumbersome tables.

Any ideas on how to do this chart more efficiently would be much appreciated. I've included some sample data below – apologies; I can't download the software allowing me to insert a proper table, so have used '/' to divide each column.

Time / Price / Volume
17:31:16 / 214 / -600
17:31:29 / 213 / 600
17:31:59 / 210 / -800
17:32:15 / 220 / 800
17:32:56 / 250 / -1200
17:33:15 / 225 / 1200

Thanks

john john

Parts Stock controller...

Win98 & or Win XP
MS-Office...using XL

Sorry for this X post as I am unsure which group to post to.

I consider myself an Excel novice student with spreadsheets etc. and have
the following problem I wish to seek help with for my son.

I have tried to devise a workbook with a parts stock control system based
on a
given manufactured item GATES, which there are variants, some years ago but
have no records of it now.!
E.G.
Gate1 is a standard sized item consisting of say 30 different components,
and fixed amounts of each of the 30 different components, made up from
stock.
Gate 2 also a standard size item but of different dimensions from Gate1
above.
This pattern carries on covering say 20 gate sizes, each one having a known
amount of components.

What I am trying to achieve is creating a stock control that would deduct
the relative compnents of any given GATE, from my stock, by entering the
GATE number as a reference point, using LOOKUP maybe?

I find it difficult to put into words, but it may mean I have to purchase a
suitable software application, but I thought I may be able to do it in Excel
somehow?........anyone know of such inexpensive applications?

TIA

Stock control..help please

Win98 & or Win XP
MS-Office...using XL

I consider myself an Excel novice student with spreadsheets etc. and have
the following problem I wish to seek help with for my son.

I have tried to devise a workbook with a stock control system based on a
given manufactured item GATES, which there are variants, some years ago but
have no records of it now.!
E.G.
Gate1 is a standard sized item consisting of say 30 different components,
and fixed amounts of each of the 30 different components, made up from
stock.
Gate 2 also a standard size item but of different dimensions from Gate1
above.
This pattern carries on covering say 20 gate sizes, each one having a known
amount of components.

What I am trying to achieve is creating a stock control that would deduct
the relative compnents of any given GATE, from my stock, by entering the
GATE number as a reference point, using LOOKUP maybe?

I find it difficult to put into words, but it may mean I have to purchase a
suitable software application, but I thought I may be able to do it in Excel
somehow?........anyone know of such inexpensive applications?

TIA

VBA code to extract m-coefficient in linear trendlines from ALL charts

I have no VBA experience and have been trying to modify a code I found
online used to extract all coefficients from the trendline textbox.
I've pretty much gotten nowhere. The problem is that it's written to
extract only the coefficients of trendline 1 in chart 1. What I would
like is to extract only the m coefficient of mx+b of all of the
trendlines in however many charts I may have in the sheet. The charts
each have 4 trendlines in them. I would like for chart 1 tline 1
extracted to D3 and tline 2 to E3. Then tline 3 to D4 and tline 4 to
E4. Chart 2 would have tline 1 to D5 and tline 2 to E5. See the
pattern? Every successive chart would have tlines 1 and 2 directly
below the previous charts 3 and 4. Also, these must be extracted to
the above cells in sheet 2. I can have as many as 20 charts in a sheet
all of which have 4 trendlines each which need to have the slopes
extracted. copy and paste gets kind of tedious after a while. Any
help would be extremely appreciated. Here's what I tried to work from:

Sub GetFormula()
Dim sStr As String, sStr1 As String
Dim sFormula As String, j As Long
Dim i As Long
Dim ser As Series, sChar As String
Dim tLine As trendline
Dim cht As Chart
Dim rng As Range
Dim varr()
ReDim varr(1 To 10)
Set cht = ActiveSheet.ChartObjects(1).Chart
For Each ser In cht.SeriesCollection
If ser.trendlines.Count = 1 Then
Set tLine = ser.trendlines(1)
If tLine.DisplayEquation Then
sFormula = tLine.DataLabel.Text '

Export Data from Cell at Set Intervals

Hi,

I have been wrestling with this for a while and am hoping someone can help.

I have a piece of trading software that can be connected to a spreadsheet. The software sends price data to cell A1 at half second intervals. Each time the price refreshes it overwrites the previous price in the same cell. I have been trying to export the price data from that cell into new cells so that for each half second refresh the price is copied into cell B1, B2, B3 etc so I end up with a single column containing a list of prices.

Is there a formula that will do this? Apologies if this is a basic question.

Thanks,

James

Picking time from dropdown box?

Hi Folks,

I've created a useform for recording stock trades. So it asks the user to enter values for

"Date of trade"
"Time of trade"
"Name of stock"
"Price"

Right now I'm using a calendar control I downloaded from
http://www.fontstuff.com/vba/vbatut07.htm and it looks good, but I'd also like to have an easy way for the user to select the time of the trade (in minutes).

Is there an easy way to have a time picker? Like a dropdown box maybe? I'd like to have date and time picked separately since the date will remain the same for a lot of trades but the time will always change.

I've googled it a lot and some people mentioned using the datetimepicker Access add-in but I'm not sure if that's right for me. Also, I'm using Excel 2007 but the form will also be used on excel 2003 machines, will that affect the choice of solution?

If this has already been discussed please let me know, or point me in the right direction or anything, many thanks for your help,
AR

Calling applications through excel ?

Hi,

I'm not sure if that's the right forum to ask that so forgive me in advance if its my bad...

I'm working at a software house, which specialized on the stock market real time trading.
Many of our clients can see the market stat through excel sheets that we've built for them. The Data of the market that shown on the excel sheets are taken from our market software. The trading is commited through the software though.

I'd like to ask.. how is there a way to make a button on excel, which will commit the trade through the excel instead of our software (and I when im saying "commit the trade through the excel..." I mean by that, is to make the trade by our software sources and applications but through the excel sheet).

I'm not sure if someone here could tell me how to do that, so even if you'd tell me a way to use other application (i.e. calculator (getting the results of certain calculation of cells on the calculator by clicking on this specific button that has been made in that case), paint (showing charts of excel on paint by clicking on this specific button that has been made in that case), or even any other applications etc,...) it'd be good enough and much appreciated.

I familiar with that should be a great deal,so I hope someone could give me a solution or idea or even some info about this.

Thank you big time and in advance
Hummiz

Stop Loss Formulas for Stock Trading

Is there any one out here that thinks they can help me with a formula for
finding a stop price for a stock? I have found an indicator for buying and
selling stocks. Now I just want to write a stop loss somehow so I can limit
my downside. My signals for Buy and Sell are pretty simple. In my
spreadsheet I have the date, open, high, low, and close. Off of those
records I used a =if(and( )) statement to determine my buy and sell points.
I wrote them like this =if(and(a3<a2), "buy", " ")and the same for the sell
side. SO then next to my open, high, low, close I have a column that is a
buy column and one that is a sell column. NOw when I get a buy I would like
to write something like this =when(e4=buy, find the value of the close that
period and had say x factor to it for the stop, if the high reaches that
point the exit the trade. Same for the sell side. DOes anybody have some
tips or suggestions. I been working on this for close to 10 hours now and I
haven't gotten anywhere. I am new to excel so would appreciate any help.
Thank you

No luck finding an answer? You could always try Google.