LearnN Stock Trading Spreadsheet for LibreOffice
and Excel (c)
Rob Hock 2024-2025
Have you heard Excel
can pull in stock market data with =STOCKHISTORY() function?
Exciting stuffing.. Let's learn by example and use this func
to pull stock price high-of-day for our trade ticker. We'll then create a chart (call it + Money Left
on Table) and add it to the menu. Come on, let's add
some func:
(LibreOffice
users wanting to pull stock quote information must search for 3rd
parties but instructions remain the same.)
BLANK TEMPLATE ROW:
The Blank Template Row
is a special row on sheet that looks like nothing (all
blanks). However, it contains a combobox (pull-down) for
selecting broker and formula for automatically applying date
stamps, calculating NET, stuff like that. It does the
magic when you demand that your trading journal allow you to
get away with inputting only the basics: ticker, price,
bot, sold
You may relocate this special
row elsewhere if it is in your way. Its location variable is
on sheet. After your BAG is sold and moved to History, the Blank
Template Row overwrites its place in THE BAGS. We
therefore want to put our variable here first.
Step 1: Find
an empty column. Last time I checked, this was "Y". The Blank
Template Row is #38 so maneuver to Y38
Step 2: Input
the following formula:
=STOCKHISTORY(C38,B38,O38,0,0,3)
Step 3: Copy
the cell to your BAGS area so it is initialized there.
Step 4: Copy
the cell to your HISTORY so it is initialized there.
Formula provided will produce
the ticker's HOD (high of day) price between date you bot/sold
security. Maybe we should juice it a little.. Try:
=(H38*STOCKHISTORY(C38,B38,O38,0,0,3))-X38
That should give us # shares
times HOD minus our wager which is max profit from the
position entry. Subtract K38 ($ banked) and we should be
there:
=(H38*STOCKHISTORY(C38,B38,O38,0,0,3))-X38-K38
If you have an older version
of Excel (2019 here), you'll need to upgrade for =STOCKHISTORY
since it is a 2021 thing I guess. I did not test the above
formula so let me know if it works kay! (These kids
now-a-days)
While stockhistory func only
gives you five pieces information, the latest greatest Excel
includes data type stock which will
throw you a myriad of stock details. If you trade with Charlie
Schwaab, their famous tool Think or Swim also has you covered
w/Excel..
THINK OR SWIM:
=RTD("tos.rtd",,"MARKET_CAP",C38)
Remember mention about
charting the security's market cap. The above formula is
useful for Blank Template Row #38 as it will return the stock
ticker's market cap to sheet. Such a chart might prove useful
to a trader such as myself as I might glean the risk/reward
happening as I trade between highly volatile micro-caps and
the usual phatties.
OKAY... LET'S CHART IT:
Here's
how to chart it and put it on the menu...
|
|
LearnN™ |
LearnN Source V2 Contact the Dock