The comments and my messages had been flooded with questions referring to why the prices in my Stock Portfolio Tracker is no longer updating accordingly.
I created a Free Google Sheet that helps you music your portfolio in accordance with transactions.
You would possibly perchance perchance perchance discover extra info in this comprehensive article here. Free Online Funding Stock Portfolio Tracker Spreadsheet.
I’m immensely overjoyed with my spreadsheet attributable to wait on then, you couldn’t in actuality accept any spreadsheet that tracked your portfolio in accordance with transactions. Most spreadsheets music your portfolio in accordance with one-time buys, which is unrealistic.
My spreadsheet would possibly perchance perchance fair give you some ideas on clear-slit how to make yours.
Surely one of the precious perfect aspects of the spreadsheet is that it auto-accumulate the impress of your stock holdings. By default, you would possibly perchance perchance depend on Google Finance’s impress quote, but Google Finance’s impress quote is handiest restricted to certain exchanges.
In my spreadsheet, I advise clear-slit systems to grab prices from Yahoo.
Nevertheless, infrequently, the impress accumulate plot breaks.
Treasure now.
Ample of us instructed me that they scrutinize the following of their very private stock tracker:
Wah, how reach luxuriate in that?
K, I really private two fixes for you. I accept each stunning clear-slit to implement.
Nevertheless, let us focus on about the field.
Why did the Yahoo Stock Quote Characteristic shatter?
Honestly, I really have not any idea.
Seemingly, monumental sites that offer these info incessantly stop of us from scalping their web space for prices. They’d reasonably you pay for it.
In essentially the most most up-to-date iteration, we kind employ of capabilities getYPrice2, or getYPrice3 to head to Yahoo and grab the prices for the respective stocks
In case you flow to the Extensions Menu, then Apps Script, you are going to be ready to search out the following capabilities:
These capabilities return “-1” attributable to they delivered a responseCode that is no longer 200. Usually, the capabilities obtained nothing from Yahoo.
Usually, they don’t work.
Now… at this level, you perchance can pronounce: “Kyith, can you fair accurate slit the BS and tell me clear-slit how to repair this thing?”
Answer 1: Employ getYPrice 2 and Alternate the Yahoo Ask URL from V7 to V6
This one will handiest work with my feature getYPrice2 and no longer getYPrice3.
Proceed to the Extensions Menu, then Apps Script, then accept getYPrice2:
Alternate that v7 to v6.
Then, refresh your Google Sheet. It could mild work.
It’s top to mild scrutinize this:
Easy accurate?
Answer 2: Pull from Financial Occasions the usage of ImportXML
Now, I’m no longer certain how the aged resolution goes to continue to work, but having one extra different is always correct.
We are able to strive to pull info from the Financial Occasions. Financial Occasions private stock and fund quotes for a quantity of varied exchanges across the field.
The downside of my resolution is that ImportXML would possibly perchance even be slower within the update, but should always you fair accurate desire a pain-free scheme of updating and don’t need essentially the latest info, then this is correct. Financial Occasions would possibly perchance trade the formatting of their web space and that will perchance perchance shatter your impress fetches.
You would possibly perchance perchance perchance scrutinize that in my trusty portfolio, I kind employ of the following plot to accumulate the facts from the Financial Occasions:
You would possibly perchance perchance perchance fair accurate put the following plot in column F2 and then amble from cell F2 downwards so as that your entire rows will private the identical plot:
=if(not(isblank(E2)), split(IMPORTXML("https://markets.ft.com/data/etfs/tearsheet/summary?s="&E2,"//li[1]/span[2]")," "),"")
This plot on the final tests that if cell E2 is no longer smooth, and has a FT Quote, then we are able to employ ImportXML to grab from https://markets.ft.com/info/etfs/tearsheet/summary? the usage of the quote you put in column E2.
Let’s pronounce, for SPDR MSCI USA Miniature Cap Rate Weighted UCITS ETF, which is listed on the London Stock Alternate the quote is USSC:LSE:USD.
So the string will kill up as https://markets.ft.com/info/etfs/tearsheet/summary?s=USSC:LSE:USD.
How kill I do know the quote is USSC:LSE:USD?
In case you flow to FT, you would possibly perchance perchance search both the fund name or the quote USSC:
But why kill I employ a Split within the plot?
“//li[1]/span[2]” will opt out the entries within the on-line space that match the most important [li] and the 2d [span].
There are eventualities where we are able to private two entries:
- The cost quote
- Other stuff
Split and ” ” enables us to resolve handiest the most important stuff, which is what we desire.
The gargantuan thing the usage of Financial Occasions is that you would possibly perchance perchance additionally update the prices for some of your unit trusts:
Within the screenshot above, I managed to efficiently pull the facts from the Dimensional World Allocation 60/40 SGD Collecting, the Dimensional World Equity SGD Collecting and the Dimensional Global Centered Rate SGD Collecting. All funds are prevalently employ by native patrons.
I attempted this with:
- Singapore stocks
- Singapore unit trusts
- London Listed ETFs and stocks
- US stocks and ETFS
I am hoping that you are stunning with each choices. In case you are no longer, I’m able to’t wait on mighty.
Attain part this with extra of us should always you deem this is able to perchance perchance wait on them.
I invested in a varied portfolio of trade-traded funds (ETF) and stocks listed within the US, Hong Kong and London.
My most in model dealer to change and custodize my investments is Interactive Brokers. Interactive Brokers imply you would possibly perchance perchance change within the US, UK, Europe, Singapore, Hong Kong and several markets. Alternatives as effectively. There are no minimal monthly charges, very low forex charges for forex trade, very low commissions for diverse markets.
To search out out extra test with Interactive Brokers on the present time.
Join the Funding Moats Telegram channel here. I’m going to part the materials, analysis, investment info, deals that I detect that enable me to flee Funding Moats.
Attain Treasure Me on Fb. I part some tidbits which would possibly perchance perchance be no longer on the blog put up there usually. You would possibly perchance perchance perchance also resolve to subscribe to my exclaim material by the email below.
I shatter down my resources in accordance with these issues:
- Building Your Wealth Foundation – In case you perceive and apply these clear-slit monetary concepts, your prolonged flee wealth wants to be stunning mighty managed. Uncover what they are
- Stuffed with life Investing – For filled with life stock patrons. My deeper ideas from my stock investing journey
- Discovering out about REITs – My Free “Route” on REIT Investing for Inexperienced persons and Seasoned Investors
- Dividend Stock Tracker – Song your entire basic 4-10% yielding dividend stocks in SG
- Free Stock Portfolio Tracking Google Sheets that many love
- Retirement Planning, Financial Independence and Spending down money – My deep dive into how mighty you’ll need to make these, and the assorted systems you will even be financially free
- Providend – Where I ancient to work doing analysis. Price-Finest Advisory. No Commissions. Financial Independence Advisers and Retirement Experts. No rate for the most important assembly to perceive the model it in actuality works
- Havend – Where I currently work. We prefer to bid commission-primarily based insurance protection advice in a greater scheme.
Kyith is the Proprietor and Sole Creator within the wait on of Funding Moats. Readers tune in to Funding Moats to be taught and get stronger, less assailable wealth foundations, clear-slit how to private a Passive investment approach, know extra about investing in REITs and the nuts and bolts of Stuffed with life Investing.
Readers also follow Kyith to be taught to realizing effectively for Financial Safety and Financial Independence.
Kyith worked as an IT operations engineer from 2004 to 2019. Within the intervening time, he works as a Senior Solutions Specialist in Insurance protection Start-up Havend. All opinions on Funding Moats are his private and does no longer describe the views of Providend.
You would possibly perchance perchance perchance peep Kyith’s most up-to-date portfolio here, which makes employ of his Free Google Stock Portfolio Tracker.
His investment dealer of need is Interactive Brokers, which enables him to put money into securities from assorted exchanges all over the field, at very low commission charges, without custodian charges, terminate to position forex charges.
You would possibly perchance perchance perchance read extra about Kyith here.
Latest posts by Kyith (scrutinize all)