Excel Retrieve Cours des actions avec une formule de feuille de calcul
Certains modèles financiers doivent faire référence à des cotations boursières à certaines dates. Au lieu d'utiliser ces comme données d'entrée codées en dur, vous pouvez créer une formule Excel qui permet de récupérer les cours des actions pour une date donnée.
Ce tutoriel utilise le concept de fonctions définies par l'utilisateur (UDF) - pour une introduction, lisez cet article. Longue histoire courte, vous pouvez développer une fonction VBA qui peut être utilisé comme une formule de feuille de calcul Excel.
Le code de ce tutoriel vous permet de récupérer les cours des actions de Yahoo! La finance. Une fois inclus, vous pouvez utiliser la formule suivante:
Excel: formule "StockQuote"
Le code, que vous devez mettre dans un module de code VBA, est la suivante:
Fonction StockQuote (strTicker As String, en option dtDate Comme variante)
«La date est facultative - si elle est omise, utilisez aujourd'hui. Si la valeur est pas une date, jeter erreur.
Si IsMissing (dtDate) Ensuite,
dtDate = Date
Autre
Dans le cas contraire (IsDate (dtDate)) Ensuite,
StockQuote = CVErr (xlErrNum)
Fin si
Fin si
Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows () As String, strColumns () As String
Dim DBClose As Double
dtPrevDate = dtDate - 7
Set http = CreateObject ( "MSXML2.XMLHTTP")
http.Open "GET", strURL, Faux
http.Send
strCSV = http.responseText
«Les informations les plus récentes est dans la ligne 2, juste en dessous des rubriques de table.
«La fin des prix est la 5ème entrée
strRows () = Duplex (strCSV, Chr (10)) 'a divisé le CSV en rangées
strColumns = Duplex (strRows (1), "") "diviser la ligne correspondante dans les colonnes. 1 signifie deuxième rangée, en commençant à l'index 0
dbclose = strColumns (4) '4 signifie: cinquième position, à partir de l'indice 0
'Debug.Print vbLf
'Debug.Print strRows (1)
'Debug.Print "DBClose:" - DBClose
Set http = Nothing
De plus, le lecteur Financial-Modelling.net René a téléchargé une vidéo d'instruction qui montre comment implémenter ce script.
Salut, j'ai sauvé la macro comme « StockQuote », mais quand je reviens à la feuille Excel et mettre dans la formule, il ne reconnaît pas la macro et dit #NOM dans la cellule où j'ai essayé d'appeler la fonction? En VB, j'ai pu nommer le projet et bien sûr la fonction à StockQuote, mais il ne dit encore que le module est appelé Module1. Retour dans la feuille où je l'ai appelé, je ne vois pas « StockQuote » ou toutes les macros sous « Voir macros ». Merci
Quelqu'un peut-il expliquer pourquoi je ne peux pas exécuter le module? J'ai coppied le code dans le module vba mais lorsque je tente de l'exécuter, le module n'est pas visible dans le viseur de de MACRO. Quelqu'un peut-il me donner un rapide tutoriel comment le faire fonctionner?
Y at-il de toute façon, nous pouvons obtenir le prix de clôture pour une date donnée qui est ajusté à diviser?
Très agréable. Cette fonction retourne la fin réelle. Je préfère utiliser la fin ajusté (corrigé des scissions et des dividendes versés). Le changement a été assez simple. Trouver la ligne
J'espère que cela pourra aider
Merci pour cette grande macro! J'ai simplifié le programme et supprimer l'entrée de la date. Donc, ma version ne sort que le prix actuel du stock ( « l1 »). Je suppose que c'est ce que Rupesh et Mike ont demandé plus haut. L'entrée doit ressembler à ceci: = StockQuote ( « TICKER_SYMBOL »).
J'ai eu des difficultés avec cette macro pour afficher des taux de change (par exemple EURUSD = X). Voilà pourquoi je l'ai changé l'URL dans ma version à « download.finance.yahoo.com ». D'une certaine façon il cela fonctionne.
Voici le code:
Fonction StockQuote (strTicker As String)
Dim strURL As String, strCSV As String
Dim DBClose As Double
Set http = CreateObject ( "MSXML2.XMLHTTP")
http.Open "GET", strURL, Faux
http.Send
strCSV = http.responseText
'Debug.Print vbLf
'Debug.Print "DBClose:" - DBClose
Set http = Nothing
Une autre chose que j'ai appris: La ligne
devrait être remplacé par
la valeur de sortie est indépendante de votre région, à savoir, peu importe si vous utilisez une virgule ou un point comme séparateur de numéro, la valeur sera comme prévu.
René, grand modifier! C'est exactement ce que je cherchais de cette macro. René, qu'en est-il réparateur?
Vous pouvez modifier l'intervalle de mise à jour (ligne 8. Il est situé à 5 minutes en ce moment). Je ne recommanderais pas de fixer un moment très petite. Yahoo semble bloquer trop de demandes du même utilisateur. Donc, si vous avez beaucoup de symboles boursiers et / ou un court intervalle de mise à jour se bloque macro pour un certain temps.
Une autre note: Après avoir copié le code dans votre feuille Excel, il pourrait être nécessaire d'enregistrer, fermer et rouvrir le dossier afin de démarrer la mise à jour automatique.
Sous auto_open ()
appelez ScheduleUpdateAll
End Sub
Sous ScheduleUpdateAll ()
TimeToRun = maintenant + TimeValue ( "00:05:00") « Régler l'intervalle de mise à jour
Application.OnTime TimeToRun, "UpdateAll"
End Sub
Sous UpdateAll ()
Application.CalculateFull
appelez ScheduleUpdateAll
End Sub
Sous Auto_Close ()
On Error Resume Next
Application.OnTime TimeToRun, "UpdateAll". Faux
End Sub
Fonction StockQuote (strTicker As String)
Dim strURL As String, strCSV As String
Dim DBClose As Double
Set http = CreateObject ( "MSXML2.XMLHTTP")
http.Open "GET", strURL, Faux
http.Send
strCSV = http.responseText
dbclose = Val (strCSV)
StockQuote = DBClose
Set http = Nothing
Merci encore,
Merle
Pourquoi est-il pas de fonction simple à tirer un prix de l'action dans Excel comme Google fournit sa feuille de calcul en ligne (= Finance (MSFT)).
Je reçois une erreur #VALUE quand j'utiliser. Quelqu'un sait pourquoi? Im » en utilisant la version modifiée de René.
Ne peut pas comprendre. J'ai ajouté le Val (.)
le code fonctionne très bien, merci! Une question, je ne peux pas sembler obtenir SPX travailler. Yahoo Finance a spx comme le symbole ^ SPXPM, et des erreurs à chaque fois. D'autres contrats à terme tels que ^ travail VIX très bien. Quelqu'un a une idée de comment résoudre ce problème?
Merci
René, Merci pour la macro! Il fonctionne très bien et était exactement ce qu'il me fallait.
Auriez-vous une idée de récupérer un autre morceau de données Yahoo Finance pour la comparaison?
Ma compréhension Macro / VBA est insuffisant pour comprendre comment adapter votre code pour effectuer cette fonction supplémentaire.
Si vous avez des idées, je serais reconnaissant.
(Je suis également un investisseur depuis longtemps et je pense qu'il vous aidera à faire de l'argent dans des actions.)
Quelques indices que je l'ai essayé ne fonctionne pas non plus comme ^ DJI et ^ SSEC.
Je suis en train de remplacer la déclaration des stocks de MyYahoo. Comment puis-je savoir quels sont les domaines tels que le changement quotidien et le pourcentage sont, de la finance yahoo?
J'aime la fonction, mais était un peu méfiants d'utiliser une fonction sur ma feuille de calcul où je informations sur la piste historique. Je ne voudrais pas mettre à jour une feuille qui a des valeurs pour une période antérieure. Donc, je l'ai écrit un peu de macro que je vais courir une fois, il va mettre à jour ma feuille avec les valeurs actuelles des stocks. Ensuite, je peux enregistrer la feuille et si je l'ouvre six mois plus tard, il ne sera pas essayer de recalcule en envoyant la commande à nouveau yahoo.
La macro est basée sur l'hypothèse que les symboles boursiers commencent à la cellule A5; il peut y avoir jusqu'à 40 symboles (blancs sont ignorés). La valeur retournée par la fonction est mis dans la cellule trois colonnes à droite du symbole.
Voici quelques exemples de données (afin que vous puissiez voir le format que j'ai):
Nom Ticker Actions Prix total
CAH Cardinal Health, Inc. 100 69,73 $ 6,973.00
Z Zillow, Inc. 200 92,27 $ 18,454.00
T AT-T, Inc 300 35,09 $ 10,527.00
Sous UpdatePrice ()
'
«Aller en bas la liste des symboles et mise à jour colonne de prix
'
Dim Symbole As String
OrigAdd = ActiveCell.Address
Range ( "A5"). Sélectionnez
Symbole = ActiveCell.Value
Pour Count = 1 à 40
Do While Symbole ""
x = StockQuote (Symbole)
ActiveCell.Offset (0, 3) .Value = x
ActiveCell.Offset (1, 0) .Activate
Symbole = ActiveCell.Value
Boucle
ActiveCell.Offset (1, 0) .Activate
Symbole = ActiveCell.Value
Suivant Nombre
Hope qui aide quelqu'un.
Sub test ()
Page Dim As Byte
Dim queryTableObject Comme QueryTable
URL Dim As String
Pour page = 1 Pour NUMBER_OF_PAGES
url = VBA.Strings.Replace (URL_TEMPLATE, « », à la page)
Set queryTableObject = ActiveSheet.QueryTables.Add (connexion: = url, Destination:. = ThisWorkbook.Worksheets.Add [a1])
queryTableObject.WebSelectionType = xlSpecifiedTables
queryTableObject.WebTables = « 3"
queryTableObject.Refresh
Page suivante
J'aime l'idée de Renee avec l'auto-updater et l'idée de Larry d'utiliser un x = StockQuote (symbole) et x = valeur de la cellule spécifiée
Mais je voudrais savoir s'il est possible de combiner un peu les deux et utiliser les prix récupérés de Yahoo. sans avoir à saisir à la formule complète avec Excel chaque fois.
Par exemple. Je veux avoir la formule intégrée dans la cellule, et tout ce que je dois taper est le AAPL de code qui convertit dans le prix (plutôt que de taper: StockQuote ( « AAPL »)
Est-ce possible??
Merci beaucoup les gars, cette macro a déjà fait mes jours beaucoup plus facile!
Je vous remercie. Tant pour l'article et le code d'origine et pour les mods de René.
Microsoft devrait vraiment fournir une fonction intégrée pour une telle données couramment utilisées définies comme les cours des actions. Et pas seulement pour l'ouvrir fermer haut bas. Mais pour la plupart des autres paramètres aussi bien. Comme P / E, PEG, offre, demande, etc. Pour les données en temps réel et historiques actuels.
Encore une fois merci à tous pour cette macro VBA très utile.
Ajout d'une option pour spécifier la valeur à retourner (strFieldName As String):
Rendez-vous amoureux
Ouvrir
Haute
Faible
Fermer
Le volume
Fermer Adj
Ou en utilisant des références de cellules pour fournir les paramètres.
Option Comparer Texte
Fonction StockQuoteHistory (strTicker As String, en option strFieldName As String, en option dtDate Comme variante)
«La date est facultative - si elle est omise, utilisez aujourd'hui. Si la valeur est pas une date, jeter erreur.
Si IsMissing (dtDate) Ensuite,
dtDate = Date
Autre
Dans le cas contraire (IsDate (dtDate)) Ensuite,
StockQuoteHistory = CVErr (xlErrNum)
Fin si
Fin si
Si IsMissing (strFieldName) Ou strFieldName = "" Alors
strFieldName = "Fermer"
Fin si
Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows () As String, strColumns () As String
Dim DBClose As Double
dtPrevDate = dtDate - 7
Set http = CreateObject ( "MSXML2.XMLHTTP")
http.Open "GET", strURL, Faux
http.Send
strCSV = http.responseText
"Positions champ Index
Si (strFieldName = "Date") = 0 alors intFieldIndex
Si (strFieldName = "Ouvrir") Ensuite intFieldIndex = 1
Si (strFieldName = "High") Ensuite intFieldIndex = 2
Si (strFieldName = "Low") Alors intFieldIndex = 3
Si (strFieldName = "Fermer") Ensuite intFieldIndex = 4
Si (strFieldName = "Volume") Ensuite intFieldIndex = 5
Si (strFieldName = "Fermer Adj") Ensuite intFieldIndex = 6
«Les informations les plus récentes est dans la ligne 2, juste en dessous des rubriques de table.
«La fin des prix est la 5ème entrée
strRows () = Duplex (strCSV, Chr (10)) 'a divisé le CSV en rangées
strColumns = Duplex (strRows (1), "") "diviser la ligne correspondante dans les colonnes. 1 signifie deuxième rangée, en commençant à l'index 0
dbclose = Val (strColumns (intFieldIndex)) '4 signifie: 5ème position, à partir de l'indice 0
'Debug.Print vbLf
'Debug.Print strRows (1)
'Debug.Print "DBClose:" - DBClose
Set http = Nothing
Fonction StockQuote (strTicker As String)
Appelez StockQuote2 (strTicker, ActiveCell.Offset (0, 1) .Address)
Fonction StockQuote2 (strSymbol As String, strDest As String)
Dim strURL As String
Avec ActiveSheet.QueryTables.Add (Connexion: = "URL;" - strURL, Destination: = Range (strDest))
.PostText = ""
.RefreshStyle = xlOverwriteCells
.SaveData = True
.Rafraîchir
Terminer par
J'utilise deux macros 1 pour l'historique des prix, et un pour le prix courant, en utilisant ce que je l'ai vu plus haut.
Tout fonctionnait bien jusqu'à aujourd'hui. Mon prix actuel (StockQuote) cesser de travailler pour moi, et retourne juste un 0. Est-ce un problème de Yahoo? A changé quelque chose? Jamais eu ce problème auparavant. Voici mon code, je suis en cours d'exécution:
Sous auto_open ()
appelez ScheduleUpdateAll
End Sub
Sous ScheduleUpdateAll ()
TimeToRun = maintenant + TimeValue ( "05: 0: 00") « Régler l'intervalle de mise à jour
Application.OnTime TimeToRun, "UpdateAll"
End Sub
Sous UpdateAll ()
Application.CalculateFull
appelez ScheduleUpdateAll
End Sub
Sous Auto_Close ()
On Error Resume Next
Application.OnTime TimeToRun, "UpdateAll". Faux
End Sub
Fonction StockQuote (strTicker As String)
Dim strURL As String, strCSV As String
Dim DBClose As Double
Set http = CreateObject ( "MSXML2.XMLHTTP")
http.Open "GET", strURL, Faux
http.Send
strCSV = http.responseText
dbclose = Val (strCSV)
StockQuote = DBClose
Set http = Nothing
Je l'utilise aussi ce code pour les prix historiques:
Fonction StockPrice (strTicker As String, en option dtDate Comme variante)
«La date est facultative - si elle est omise, utilisez aujourd'hui. Si la valeur est pas une date, jeter erreur.
Si IsMissing (dtDate) Ensuite,
dtDate = Date
Autre
Dans le cas contraire (IsDate (dtDate)) Ensuite,
StockPrice = CVErr (xlErrNum)
Fin si
Fin si
Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows () As String, strColumns () As String
Dim DBClose As Double
dtPrevDate = dtDate - 7
Set http = CreateObject ( "MSXML2.XMLHTTP")
http.Open "GET", strURL, Faux
http.Send
strCSV = http.responseText
«Les informations les plus récentes est dans la ligne 2, juste en dessous des rubriques de table.
«La fin des prix est la 5ème entrée
strRows () = Duplex (strCSV, Chr (10)) 'a divisé le CSV en rangées
strColumns = Duplex (strRows (1), "") "diviser la ligne correspondante dans les colonnes. 1 signifie deuxième rangée, en commençant à l'index 0
dbclose = strColumns (4) '4 signifie: cinquième position, à partir de l'indice 0
'Debug.Print vbLf
'Debug.Print strRows (1)
'Debug.Print "DBClose:" - DBClose
Set http = Nothing
Toute aide à la raison pour laquelle mon StockQuote cesser de travailler serait appréciée.
Je vous remercie!