Previous Topic Next Topic

How to capture highest/lowest values/dates over time

Closed
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013 - Oct 11, 2013 at 04:36 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 14, 2013 at 05:28 AM
I'd be grateful if anyone knows the answers.

I've been using an Excel spreadsheet to determine the total value of stocks I own.

Basically I enter stock prices for each stock on my list periodically (using a desktop stock ticker as my reference), and the worksheet has fields containing the number of shares I own of each, so it calculates the value of the shares as I enter (today's) prices.

The top of the Excel spreadsheet has a date field that reflects the current date... "=now()"

I have a total field where all the stock values are totaled and 4 other fields where I try to keep track of the highest total value (since the sheet was started) and the date that the high occurred, and the lowest total value, and the date that low occurred... but I've been entering data in those 4 fields manually (assuming I notice there's been a new high or a new low).

There is not a column for each day of the year, the Excel spreadsheet only covers the values for today,
it does not hold historical values.

Seems like there should be a way to have the excel worksheet determine if the calculated value is higher than the previous high value, or lower than the previous low value, and change the fields accordingly.

I also need a way to capture the current date as a constant, rather than the function =now()

Anybody know how?

Here's a very simplified version, hope it formats ok when I post...
(I realize that in the example below I may need to keep extra fields for previous lows and highs)

Date: 10/11/2013

today's calc.........Stock......Price.....Shares
........$4.00.............A...........$4.00.......1
......$25.00.............B...........$5.00.......5
......$24.00.............C...........$3.00.......8
........$7.20.............D...........$2.40.......3
......$67.20.............E...........$6.00.....11.2
......$12.00.............F...........$1.00......12
......$65.00.............G...........$5.00.....13

total of shares x price... $204.40

............................................total....................................date
highest total & date.......$218.25....occurred on........6/1/2013
lowest total & date,,,,,,,,,$203.60....occurred on....10/11/2013
Related:

6 responses

Answer 1 / 6
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 12, 2013 at 07:40 AM
in USA thkre mustbe multiple stock downloader

exsample

https://investexcel.net/multiple-stock-quote-downloader-for-excel/
0
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 12, 2013 at 12:23 PM
Wow! That stock price downloader is way, way above my level of expertise!

Thanks, I'll try to digest it, it would be nice to automatically download my stock prices.
That downloader retrieves historical data for each stock on it's own separate spreadsheet
and would be good for finding the high and low prices for individual stocks over the year; my needs are much simpler, and my Excel expertise is very low.

My main problem is just capturing high and low portfolio totals and the dates they occur (as in my simplistic example). I don't have a need to download and keep historical price data on every stock. I see how that spreadsheet might be used to find those high and low dates by stock, or even extended to show the portfolio highs and lows for the year, I'm just not sure I could master that.

I'll Google for simpler available spreadsheets, thanks for the pointer.
0
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 13, 2013 at 12:34 PM
The stock price downloader spreadsheet is beyond my level of competence. Actually, even mult-page spreadsheets are beyond my level of competence. It opens with a security warning about viruses in macros; if I enable macros, it shows me a spreadsheet with a button labeled "Get Bulk Quotes". Clicking the button results in a Visual Basic error '438'. "Object doesn't support this property or method".
0
Answer 2 / 6
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 12, 2013 at 11:33 AM
Sorry, I see a slight error in my simplified example spreadsheet:
the last "occurred on" date should be earlier than today's date on the spreadsheet,
since it doesn't equal the calculated value. Let's make it, say, 03/22/2013
0
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 12, 2013 at 03:40 PM
Oh, and I'm using Excel 2003 (too cheap to buy a newer version of Office, 2003 does everything I've needed to do).
0
Answer 3 / 6
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 12, 2013 at 09:35 PM
there is an add in called smf addin. it takes yahoo codes for stocks. I used sometime back. however recently it does not work yahoo INDIA stocks. however it must be workfing for USA stocks. if you can tweak with the macro it makbe lpossible to get high and low dail. then you arcivae it in another sheetdaily for which a macro can be written. both for downloading and archiving.

http://fairvalueinvestor.com/addin.html
0
Answer 4 / 6
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 13, 2013 at 11:06 PM
there is no provision to attach a file i this newsgroup.
you download this file from this web page

http://speedy.sh/x4mFq/wayne-131014.xlsm

iln sheet 1 you type the ticker symbol of the stock IBM,yhoo(for yahoo) GOOG(for google) etc
start date and end date you can change

clcik the btton "get data from yahoo"

see second sheet named "data"

this is from a web page given at the top in sheet1

see whether thils will be of help to you
0
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 14, 2013 at 01:46 AM
I appreciate your trying to write a spreadsheet for me to retrieve stock prices from Yahoo, but I was unsuccessful in downloading and executing it... when I clicked on the download, my Firefox browser asked what it should do with the file, and the first choice looked proper: "Open with Microsoft Office Excel", but when I made that choice, Excel came up with only an error message: "The converter failed to open the file". Same result if I save the download file and then double-click it.

Although it would be nice to download stock prices from Yahoo, the real reason for my post is to find a way for the Excel spreadsheet to determine if today's portfolio total is larger or smaller than the highest or lowest total previously recorded on the spreadsheet (my simple example) and if so, to replace the highest total and the date it occurred (on the spreadsheet) with today's total and date (likewise the lowest total and date). Everything I've tried to change the previous high or low total results in circular reference errors. Beyond that, I don't know how to capture the date in an =now() date field (today's date) and store it in another cell as a constant (eg: as 10/15/2013 rather than "=now()").

Sorry, I'm just not very knowledgeable on Excel, and working with Excel files with multiple sheets are beyond my knowledge. Perhaps someone could point me to a simply-written tutorial with lots of examples that would increase my knowledge.

Thanks for trying to help.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 14, 2013 at 01:15 AM
I got a modification of this for more than one stocks see this file

download this file

http://speedy.sh/buFJM/WAYNE-MUTISTOCK-DATA-131014.xlsm

now enter start date and end date and hit enter
the list of tickers are given below . add or reduce
click the button "get bullk quotes"

you will get differesnt sheets named as the ticker symbols and you can see the data

CONFIRM THIS IS USEFUL OR NOT
0
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 14, 2013 at 02:15 AM
Thanks, Venkat1926, this latest Excel file did execute, sort of.... it comes up initially with only "Parameters" and "WMT" sheets. If I change the date range, it gives me a small run-time error '438' window; when I click "end" on the error window it brings up the second sheet "WMT" with whatever date range I chose (there are no worksheets for the other six stocks in the list).
0
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 14, 2013 at 03:02 AM
Forgot your "is it useful" question.... it could be useful, if it worked and if I knew how to refer to data on other worksheets from cell formulas on the main sheet, and how to inspect the data on the other worksheets with cell formulas on the main worksheet, and how to incorporate this type of Excel coding into my own Excel files.
But, I don't.
For example, can there be a cell next to WMT on the Parameters sheet that can inspect the High and Low columns of fields returned on the WMT worksheet and find the highest and lowest values therein and display those values in fields next to WMT on the Parameters worksheet?

I see where the Excel file you referred me to appears on the Speedyshare website.
In the discussion they keep referring to "VBA".... is that shorthand for Visual Basic?
(I'm an old retired mainframe guy, so I know just barely enough about computers to be dangerous to myself)
How do you look at the code that comprises this Excel file?

Are you Samir Kahn?
0
waynec Posts 9 Registration date Friday October 11, 2013 Status Member Last seen October 14, 2013
Oct 14, 2013 at 03:40 AM
Forgot to answer your question about whether it was useful...

It would be if it worked properly, and if I knew how to make alterations to it, and if I knew how to write code for cells on the Parameters sheet that could access data on the stock worksheets.
For example, if on the "Parameters" sheet I could inspect the "High" column on the WMT worksheet and insert those values and the corresponding date into fields next to the "WMT" on the "Parameters" worksheet.

I'm an old retired computer mainframe guy, so I know barely enough to get myself in trouble.
On the website you reference, messages from users keep referring to "VBA"... I assume that is shorthand for Visual Basic? I've no idea how to use Visual Basic, and certainly not how to use it with Excel.

I see where you got the Excel file on the InvestExcel.net website you referred me to.

Are you Samir Kahn?

Again, thanks for your interest in my post.
Time for bed, I'll check again tomorrow.
0
Answer 6 / 6
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 14, 2013 at 05:28 AM
thIS can be done. I have to work out a macro for trnsferring the data from the stock sheets and trnsfering to another new sheet for todays and yesterday's data high and low and manipulate. That reqes you must be familiar with macros and how to run thm. It IS NOT ROCKET SCIENCE. even old people can LEARN it. I myself am a senior citizen. you will be surprised if I tell you my age but that is the secret. I suggest if you hve time learn excel including visual basic for application and macros. get some good books on 1) excel and 2) excel vba YOU CAN LEARN IT AND IT WILL BE USEFUL TO MONITOR STOCKS AND DO STOCK ANALYSIS.

my name is venkat and I am not samir khan.
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
The system time is invalid
LeonardPromise -
Anonymous User -

5 responses