Model Stock Price Movements In A Column

Aug 1, 2008

I have a spreadsheet that tries to model a possible stock price movement in a column. So in column A I have all dates starting from today and in Column B a specific stock's possible stock price movemens onver the next 2 years.

So dates start at A3 and stock prices start at B3 and end at A734 and B734.

This gives me ONE possible stock movement and to get the most accurate movement, I will need to model this 20,000 times.

Therefore, I want to create a loop macro that repeat this motion 20,000 times in the columns next to A and B. So which loop effect would I need to use and how would the code look like ? This is honestly my first time attempting to use a loop macro

View 9 Replies


Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day

Jun 9, 2008

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies View Related

VBA Code / Stock Historical Price Macro?

Jul 10, 2012

I am looking for this one function i need for a school project on historical stock prices in excel.....basically i need a way to perform this function:

In row 1, i have 30 RANDOM historical dates, starting in B1 and going right
In column A, i have 10 stock symbols, starting in A2 and going down

Is there a way to automatically grab the ADJUSTED CLOSING PRICE for each symbol for each corresponding date?? if not i have to do it manually!!

View 3 Replies View Related

Linking Stock Price Quotes From External Source

Jul 5, 2014

I would like to create an Excel worksheet with links to external real-time sources for stock quotes so that I can evaluate information based on real-time stock quotes.

View 1 Replies View Related

Track High Price Of Stock From Date Of Purchase?

Dec 5, 2013

Any cleanest way to track the high-water mark of the price of a stock I own ONLY AFTER my date of purchase.

Each day I log on, a VLookup pulls up the recent stock price. How can I create a formula that will store this price and compare it to the stock price the next time I log in and save the highest of the two prices for the next day's comparison.

Day 1 $10
Day 2 $11- The $10 is replaced by $11
Day 3 $10- The $11 is retained.
Day 4 $12- The $11 is replaced by $12.

View 3 Replies View Related

How To Implement Real-time Stock Price(s) Into Excel File

Aug 1, 2014

Currently I am using MS office 2000 premium. I would like to implement real-time stock prices (i.e. ticker MSFT) from the Nasdaq website (NASDAQ Stock Market - Stock Quotes - Stock Exchange News - into a single cell, so that this cell shows the current stock price only.

By a click on the hyperlink symbol in the excel sheet I entered the following information... Microsoft Corporation (MSFT) Real-Time Stock Quote - and clicked the OK button.

Instead of receiving the real-time stock price information directly into the single cell, a new window opens with the Nasdaq website and all kinds of information that is not needed.

View 2 Replies View Related

Using VBA In Patch Model To Change VBA In User Model

Jan 11, 2007

I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.

The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.

The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.

View 9 Replies View Related

Cell Update With Real Time Stock Quotes For Particular Stock

Aug 7, 2012

What is the easiest way to have a cell update with real time stock quotes for a particular stock?

View 4 Replies View Related

Stock Order Sheet To Be Created From Stock Inventory

Jan 8, 2014

I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.

I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?

This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?

View 7 Replies View Related

Controlling Cursor Movements

Sep 5, 2009

I have a workbook that contains spreadsheets that serve as input sheets to generate reports (other spreadsheeets within the workbook). I did not create user forms as I find them cumbersome to do so. Instead I formatted the input sheets to be extremely user friendly. I protected the sheets so that user can only access the unlocked cells that require inputs.


Is it possible to control the movement of the cursor by both tab and enter like is done in the user forms. The input cells are spread out in various locations on the spreadsheet - therefore, I would like to drive the direction of the cursor as to what cell to go next after an input is made and entered by either hitting the enter button or tab button.

View 9 Replies View Related

Inventory Application: Track The Movements Of The Items

Feb 17, 2009

I am building an inventory application in 2007. I need to track the movements of the items so I was planning on a worksheet containing the item no, date, location.
Since an item will move around and thus have multiple records, how can I extract only the latest date record for each item and store them in another worksheet ?

So I would have 2 worksheets, a. transactions, and b. current inventory. Or am I trying to reinvent the wheel. I am sure there are a lot of inventory applications already written - how do I find one? So, 2 question!..

View 5 Replies View Related

Excel VBA - Cursor Movements Based On Conditions

Apr 20, 2014

After I did the conditional formatting, I wanted the cursor to skip the colored cells. I used this code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Moves the active cell to the first column when enter button is pressed
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 12
Cells(Target.Row, Target.Column + 2).Select
Case Else
Exit Sub
End Select

But to no avail it applies to the whole sheet and I did this for "Condominium". How do I make it as a condition when I choose any property type? and then it'll go one step down to column A when the last row is typed/filled in?

For example, if I choose Condominium as my property type, Land Size (i.e. column M) is colored so the cursor will go from column L to Column N. If I choose Land as my property type, Furnished to Floors total (i.e. columns I to L) Bedrooms to Rooms Total (i.e Columns O to Q) Indoor Features to Other Features (i.e. Columns T to W) are colored so the cursor will go from column H to M then Column N to R and then Column S and then move one step below to column A when entered. and so on.

sample sheet.png

View 3 Replies View Related

Recogising Various Products In 1 Column + Returning In Or Out Of Stock In Another

Apr 16, 2008

I want to produce a spreadsheet which automatically tidies up a transferral of data from a programme called sage in to a customer service spreadsheet for my company.

I am a bit of a novice but a keen one so please be patient! I do know what I want to achieve in the long run!

The problem I have at the moment is recognising products in one column and getting it to return the value in or out of stock in another. So far I have got it to recognise one product and return the correct statement (using the "IF" function) but i need it to do it for the whole column and for a range of products.

View 13 Replies View Related

Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))


If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies View Related

Sum Extended Price Without Extended Price Column

Jul 14, 2009

I have a unit price and a quantity. I want to be able to take the sum of the extended price without having to add a column for extended price. I don't want to just hide it, either.

Example attached.

View 2 Replies View Related

How To Populate Price In One Column By Marking Another Column

May 25, 2013

I am creating an inventory list for a lab, I have a column called "reorder" and another column called "price". Is it possible to have a checkbox, or if I put "x" in the reorder column, the price for that particular item will appear in the "price" section.

Then I will have a total field and any prices that populated will calculate the total price?

View 1 Replies View Related

How To Create Stock Column Chart With Error Bars (Both Gender Together)

Jan 3, 2013

I have a series of data and want to create a chart looking like this

Google Image Result for [URL] ...

Where it shows min/max on the error bars, quartile 1/3 on the box and median as a scatter plot. My data is a simple table

Amount F
Amount M
Median F
Median M
Median F+M

[Code] ....

Here F = Female, M=Male and the chart should show all calculation for both gender together (I have another thread up where the gender are separated with the Title (How to create a stock column chart with error bars?? (Both gender separated))

View 1 Replies View Related

Lookups For An Item's Price From A Price List

Jan 4, 2010

The analysis basically has 2 data components to it:
The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.

The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.

What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:

1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)

2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)

3) Pull the price for this "closest quantity"

I have uploaded a worksheet showing the structure of that data.

Is there some VB code I need to do this, or can it just be a few simple formulas?

View 4 Replies View Related

Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)

Jul 24, 2006

All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)

Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines

I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,

View 7 Replies View Related

Looking For The Closest Price To A Reference Price

Feb 12, 2010

I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?

I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) just didn't work for some lines, and only worked for values less than source price in other instances.

I would also like to reference the source on the next column.

View 12 Replies View Related

If Statement: If The 2009 Price Is .50 Or Less Away From The 2008 Price, Bring Back "Check"

Mar 17, 2009

I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009
$23.95 $24.15

Using excel 2007

View 3 Replies View Related

Find A PORTION Of Text In A D Column And Automatically Adds The Price To The Right Of It Together In One Cell

Mar 22, 2009

I am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase.

My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks

View 2 Replies View Related

Making Average Buy Price And Average Sell Price

Aug 13, 2008

to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.

View 9 Replies View Related

Model For Payment Schedule

Aug 20, 2013

I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.

I've started with a formula


But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).

View 2 Replies View Related

Change Model To Category?

Apr 13, 2014

1. In column A, I have models, in column B, I want to place either a "printer" if the model begins with Phaser or Laser, and if it begins with WorkCentre make it MFD. Also, if the Phaser has MFP on the end, make this an "MFD" and If Color 560 or Docucolor, make this "Out of Scope". How do I read the first part of the string in column A to do this, or do I need to make a second column with this Phaser and WorkCentre broken out?

I have this code attached, but only works if I have the word Phaser or WorkCentre.

View 8 Replies View Related

Spreadsheet Model & Buttons

Dec 20, 2006

I want to create a button on this spreadsheet - the button will be called z4 in the "Yr 9 ICT" sheet

it will include the following

rows Jane Bloggs 2
rows Jane Bloggs 12
rows Jane Bloggs 22
rows Jane Bloggs 32
rows Jane Bloggs 33
rows Jane Bloggs 42
rows Jane Bloggs 44
rows Jane Bloggs 66

What do I have to do to create this.

View 10 Replies View Related

Time Model Graph

Jul 1, 2007

I have a sheet with a table of peoples hours they are doing during the week each day. I want the hours to be put into a model shading the appropriate times to show when people will be in. I have attached a sample sheet of what i want it to do which i have had to do my self. Sheet 1 has table in and sheet 2 has the model i want to be automatically filled in once ihave entered all my data in the original table.

View 11 Replies View Related

Simulation Model For Inventory

Nov 15, 2006

I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).


Below are Company B's sales records for the past 36 months.


View 5 Replies View Related

Formula For A Deferred Revenue Model?

Dec 27, 2013

I have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.

View 4 Replies View Related

Solver With Code-Generated Model

Dec 14, 2009

Suppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go.
So far so good.

And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.

Now one of the two:.....

View 4 Replies View Related

Copyrights 2005-15, All rights reserved