Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Excel 2010


30 Apr 2011   #1

W7 X-64 RTM,SUSE 11.1, XP PRO SP3 as a VM, VMware ESXi
 
 
Excel 2010 Add forms / 2nd spreadsheet to a column

Hi all
I'm trying to add a little Calculator function to a Spreadsheet I use for trading shares.

I pull basic data from YAHOO finance and would like to have at the bottom of a spreadsheet a calculator which would allow me to enter Nr of shares bought, Price Paid and return things like Profit / Loss.

The advantage of having it on the spreadsheet is then it's all in the same place.

The spreadsheet looks something like this -- enclosed pic

I'd like to embed the calculator in column M say starting at Row 15-- I'm not sure whether this should be done with a FORM or embed another spreadsheet.

Any ideas / links --- all the googling I've done either quote examples that are so impossibly complicated or hard or just too basic.

Cheers

jimbo



Attached Thumbnails
Excel 2010-options.png  
My System SpecsSystem Spec
.

30 Apr 2011   #2

Windows 7 Home Premium 64bit
 
 

It is possible that there already exists a function formula for the purpose:
Excel functions (by category) - Excel - Microsoft Office

e.g. PV - Returns the present value of an investment


Edit:
I just came across this, which might also be useful:
Excel VLookup Function and VLookup Example
My System SpecsSystem Spec
30 Apr 2011   #3

W7 X-64 RTM,SUSE 11.1, XP PRO SP3 as a VM, VMware ESXi
 
 

Hi there
What I meant was to insert a form on the spreadsheet so the user could enter the relevant data and get the results the actual formulae themselves isn't a problem
but I'd like to have the calculator on the spreadsheet

For example using the standard windows calculator

cheers
jimbo


Attached Thumbnails
Excel 2010-options2.png  
My System SpecsSystem Spec
.


30 Apr 2011   #4

Windows 7 Home Premium 64bit
 
 

Quote   Quote: Originally Posted by jimbo45 View Post
Hi there
What I meant was to insert a form on the spreadsheet so the user could enter the relevant data and get the results the actual formulae themselves isn't a problem
but I'd like to have the calculator on the spreadsheet

For example using the standard windows calculator
Ah, now I see what you mean. I'll put my thinking cap on..
My System SpecsSystem Spec
30 Apr 2011   #5

Windows 7 Home Premium 64bit
 
 

Probably not exactly what you are looking for, but a small step nearer:
Add Calculator In Excel 2010


Quote:
By default this feature is hidden.
To make it apparent, navigate to Quick Access toolbar options and click More Commands.


It will lead you to Excel Options dialog, under Choose commands, click drop-down button to select All Commands. Now scroll-down to find Calculator command and click Add >> to show it in Quick Access toolbar. Hit OK to continue.

Click Calculator button in Quick Access toolbar to immediately open Calculator. Now you can use it for simple calculations
My System SpecsSystem Spec
01 May 2011   #6

W7 X-64 RTM,SUSE 11.1, XP PRO SP3 as a VM, VMware ESXi
 
 

Hi there
Thanks

Great tip -- but I've finally found out what to do

Shown in the diagram

Thanks for the ideas anyway

Cheers
jimbo


Attached Thumbnails
Excel 2010-options_calc.png  
My System SpecsSystem Spec
01 May 2011   #7

Windows 7 Home Premium 64bit
 
 

You're welcome Jim. I just wish I could persuade our shares to yield more!
My System SpecsSystem Spec
02 May 2011   #8

W7 X-64 RTM,SUSE 11.1, XP PRO SP3 as a VM, VMware ESXi
 
 

Hi Irene

That's not my actual portfolio !! -- I'm just starting to look at OPTION TRADING (Weekly) -- The list of stocks is the available stocks for which Options trade WEEKLY.


The calculator was to calculate possible profit on a particular option at expiry -- and since weekly options expire every week the profit is per 7 days which can be HUGE since there are actually only 5 trading days where money is potentially at risk -- so if you have say a stock worth 20 USD and you SELL a CALL on it at a strike price of say 21 USD for a premium of 0.5 USD a share you've collected REGARDLESS of whatever happens to the stock 50 USD per contract (Options contracts are always per 100 shares)

So your Gain is 50/1950 * 100 % = 2.56%
-- Not bad for 5 days work. And you keep the stock-

- Don't forget that the amount of money at risk is NOT the 2000 the stock costs but LESS the 50 USD PREMIUM the you get for writing (i.e SELLING) the call. 99% of people forget this and get the %'s wrong since they use the original investment as the base - but that's clearly wrong --say the stock goes to ZERO you've still got the 50 USD so the MAX money at risk is obviously 2000 - 50.

If the stock RISES above the "Strike" price You will be "Called" i.e you HAVE to sell the stock at 21 USD -- so you've STILL made the 50 USD on the Call premium PLUS 1 USD profit on the stock per share = 100 USD so your profit % gain in the week is 250/1950 * 100% = 12.82%

For PUTS its the reverse -- people who are interested can google on selling CALLS and PUTS

(Note calculations are for SELLING options --around 90% expire without being Called or PUT so can be profitable if done carefully

Annualize that and quite decent profits can be made - makes the Banks with their paltry 3% A YEAR look stupid.

That was what I was trying to do on the spreadsheet

As an example Your trading screen will look something like this (screen attached) -- I use OptionsXpress but they are all roughly similar

Here's a list of the Option Chain for PCX for Week 1 of May -- expires on Friday 6 May.

For a STRIKE of 26 USD you can get nearly 50 Cents a share for selling the CALL option.

Anyway this is primarily a Windows Forum rather than a Stock exchange Forum but you can get the idea of some great things you can do in the newer versions of EXCEL which make things like getting data from the web a doddle.

Cheers

jimbo


Attached Thumbnails
Excel 2010-options3.jpg  
My System SpecsSystem Spec
02 May 2011   #9

Windows 7 Home Premium 64bit
 
 

I'll pass this on to my husband.
My System SpecsSystem Spec
02 May 2011   #10

W7 X-64 RTM,SUSE 11.1, XP PRO SP3 as a VM, VMware ESXi
 
 

Hi Irene
I see you are in the UK -- for the examples I've quoted you have to trade the US market -- however OptionsXpress based in Amsterdam - Head office in Chicago- allows European residents to open accounts and deal on US markets -- you need to sign a declaration you aren't resident in the USA so are "Witholding Tax exempt" -- then its fine.

Great time in London during the wedding.

Back at work now -- not a holiday here.

Cheers
jimbo
My System SpecsSystem Spec
Reply

 Excel 2010




Thread Tools



Similar help and support threads for2: Excel 2010
Thread Forum
Excel 2010 Microsoft Office
Solved Excel 2010 help Microsoft Office
excel 2010 Microsoft Office
Formatting Excel 2010, Excel 2003 Microsoft Office
Excel 2010 and Web Queries Microsoft Office
Excel 2010 sorting Microsoft Office
Incredible Excel 2010 Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd

All times are GMT -5. The time now is 04:11 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33